高手帮忙了...关于表的嵌套查询

显示全部楼层 · 2007-9-9 01:05:40

有个棘手的问题想请教下了...关于Sql server 2000,高校学生宿舍信息管理系统，信息系统的课设
表一， “宿舍信息表” DB_DormInfo 字段为 dormId(自动编号),buildArea(地理区域),buildNo(宿舍楼号),dormNo(宿舍号),bedNum(床位数),buildElse(备注)
表二，“ 入住登记表” DB_DormRegister 字段为 stuNo(学号),buildArea(地理区域),buildNo(宿舍楼号),dormNo(宿舍号)
问题是
现在想查询人数还未满的宿舍号，该怎么写sql语句...想了一个晚上，都快崩溃了...希望高手帮帮忙

千问 · 2007-9-9 01:05:40

Code Snippetdeclare @DB_DormInfo table
(
dormId int identity(1,1),
buildArea varchar(20),
buildNo varchar(10),
dormNo varchar(10),
bedNum int,
buildElse varchar(100)
)
declare @DB_DormRegister table
(
stuNo varchar(20),
buildArea varchar(20),
buildNo varchar(10),
dormNo varchar(10)
)
insert @DB_DormInfo select '南区','1号楼','601',5,''
union all select '南区','2号楼','602',4,''
union all select '北区','1号楼','501',5,''
union all select '北区','2号楼','502',6,''
union all select '北区','3号楼','503',8,''
insert @DB_DormRegister select '0001','南区','2号楼','602'
union allselect '0002','南区','2号楼','602'
union allselect '0003','南区','2号楼','602'
union allselect '0004','南区','2号楼','602'
union allselect '0001','北区','1号楼','502'
union allselect '0001','北区','2号楼','503'

select
a.*
from @DB_DormInfo a
where bedNum > (
select count(*)
from @DB_DormRegister
where buildArea = a.buildArea
and buildNo = a.buildNo
and dormNo = a.dormNo
)
/**
1 南区 1号楼 601 5
3 北区 1号楼 501 5
4 北区 2号楼 502 6
5 北区 3号楼 503 8

**/

千问 · 2007-9-9 01:05:40

Code Snippetdeclare @DB_DormInfo table
(
dormId int identity(1,1),
buildArea varchar(20),
buildNo varchar(10),
dormNo varchar(10),
bedNum int,
buildElse varchar(100)
)
declare @DB_DormRegister table
(
stuNo varchar(20),
buildArea varchar(20),
buildNo varchar(10),
dormNo varchar(10)
)
insert @DB_DormInfo select '南区','1号楼','601',5,''
union all select '南区','2号楼','602',4,''
union all select '北区','1号楼','501',5,''
union all select '北区','2号楼','502',6,''
union all select '北区','3号楼','503',8,''
insert @DB_DormRegister select '0001','南区','2号楼','602'
union allselect '0002','南区','2号楼','602'
union allselect '0003','南区','2号楼','602'
union allselect '0004','南区','2号楼','602'
union allselect '0001','北区','2号楼','502'
union allselect '0001','北区','3号楼','503'

select
a.dormId,
a.buildArea,
a.buildNo,
a.dormNo,
a.bedNum
from @DB_DormInfo a
left join @DB_DormRegister b
on b.buildArea = a.buildArea
and b.buildNo = a.buildNo
and b.dormNo = a.dormNo
group by a.dormId,a.buildArea,a.buildNo,a.dormNo,a.bedNum
having a.bedNum > count(b.stuNo)