sql问题

Question

表stuindorm
学号     区域    楼号   宿舍号
064860247 1 1 101
064860248 1 1 101
064860249 1 1 102
064860250 1 1 103

表dorm
区域  楼号 宿舍号 床位数
1 1 101 6
1 1 102 6
1 1 103 6
1 1 104 6
1 1 105 6
1：想查找每个寝室剩余的床位数。。
2：把还有空余床铺的宿舍信息查找出来。。 
两个分开的sql语句 谢谢declare @num int
select @num=count(sno) from studentindorm where rid=1 and bid=1 and did=101
select dorm.rid,dorm.bid,dorm.did,dbednum-@num as '剩余床位数' from dorm,studentindorm where dbednum-@num>=0 
and studentindorm.rid=dorm.rid and studentindorm.bid=dorm.bid 
and studentindorm.did=dorm.did以上自己写的不对。。

Answer 1

---1
select a.区域,楼号,宿舍号,床位数-isnull(b.num,0) as '剩余床位数'  
from dorm a 
left join(
select 区域,楼号,宿舍号,count(1)as num 
from stuindorm
group by 区域,楼号,宿舍号) b 
on a.区域=b.区域 and a.楼号=b.楼号 and a.宿舍号=b.宿舍号---2
select a.区域,楼号,宿舍号,床位数-isnull(b.num,0) as '剩余床位数'  
from dorm a 
left join(
select 区域,楼号,宿舍号,count(1)as num 
from stuindorm
group by 区域,楼号,宿舍号) b 
on a.区域=b.区域 and a.楼号=b.楼号 and a.宿舍号=b.宿舍号
where 床位数>isnull(b.num,0)

Answer 2

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-04-23 14:02:52
-- Version:
--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 
-- Oct 14 2005 00:33:37 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：#stuindorm
if object_id('tempdb.dbo.#stuindorm') is not null drop table #stuindorm
go 
create table #stuindorm([学号] varchar(9),[区域] int,[楼号] int,[宿舍号] int)
insert #stuindorm
select '064860247',1,1,101 union all
select '064860248',1,1,101 union all
select '064860249',1,1,102 union all
select '064860250',1,1,103
--> 测试数据：#dorm
if object_id('tempdb.dbo.#dorm') is not null drop table #dorm
go 
create table #dorm([区域] int,[楼号] int,[宿舍号] int,[床位数] int)
insert #dorm
select 1,1,101,6 union all
select 1,1,102,6 union all
select 1,1,103,6 union all
select 1,1,104,6 union all
select 1,1,105,6
--------------开始查询--------------------------
select b.*,a.[床位数]-b.num
 from #dorm a,
(select 
[区域] ,[楼号] ,[宿舍号],count(1) num 
from #stuindorm group by [区域] ,[楼号] ,[宿舍号])b where a.[区域]=b.[区域]  and a.[楼号]=b.[楼号] and a.[宿舍号]=b.[宿舍号]
----------------结果----------------------------
/*
 
(4 行受影响)(5 行受影响)
区域          楼号          宿舍号         num         
----------- ----------- ----------- ----------- -----------
1           1           101         2           4
1           1           102         1           5
1           1           103         1           5(3 行受影响)
*/

Answer 3

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-23 14:08:53
-- Verstion:
--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 
-- May 26 2009 14:24:20 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[stuindorm]
if object_id('[stuindorm]') is not null drop table [stuindorm]
go 
create table [stuindorm]([学号] varchar(9),[区域] int,[楼号] int,[宿舍号] int)
insert [stuindorm]
select '064860247',1,1,101 union all
select '064860248',1,1,101 union all
select '064860249',1,1,102 union all
select '064860250',1,1,103
--> 测试数据：[dorm]
if object_id('[dorm]') is not null drop table [dorm]
go 
create table [dorm]([区域] int,[楼号] int,[宿舍号] int,[床位数] int)
insert [dorm]
select 1,1,101,6 union all
select 1,1,102,6 union all
select 1,1,103,6 union all
select 1,1,104,6 union all
select 1,1,105,6
--------------开始查询--------------------------
select
 b.*,a.[床位数]-b.num
from
 dorm a
join
(select [区域] ,[楼号] ,[宿舍号],count(1) num from stuindorm group by [区域] ,[楼号] ,[宿舍号])b
on
 a.[区域]=b.[区域] and a.[楼号]=b.[楼号] and a.[宿舍号]=b.[宿舍号]----------------结果----------------------------
/* 区域          楼号          宿舍号         num         
----------- ----------- ----------- ----------- -----------
1           1           101         2           4
1           1           102         1           5
1           1           103         1           5(3 行受影响)*/

Answer 4

create table stuindorm([学号] varchar(9),[区域] int,[楼号] int,[宿舍号] int)
insert stuindorm
select '064860247',1,1,101 union all
select '064860248',1,1,101 union all
select '064860249',1,1,102 union all
select '064860250',1,1,103create table dorm([区域] int,[楼号] int,[宿舍号] int,[床位数] int)
insert dorm
select 1,1,101,6 union all
select 1,1,102,6 union all
select 1,1,103,6 union all
select 1,1,104,6 union all
select 1,1,105,6
---1
select a.区域,a.楼号,a.宿舍号,床位数-isnull(b.num,0) as '剩余床位数'  
from dorm a 
left join(
select 区域,楼号,宿舍号,count(1)as num 
from stuindorm
group by 区域,楼号,宿舍号) b 
on a.区域=b.区域 and a.楼号=b.楼号 and a.宿舍号=b.宿舍号         区域          楼号         宿舍号       剩余床位数
----------- ----------- ----------- -----------
          1           1         101           4
          1           1         102           5
          1           1         103           5
          1           1         104           6
          1           1         105           6(5 行受影响)---2
select a.区域,a.楼号,a.宿舍号,a.床位数-isnull(b.num,0) as '剩余床位数'  
from dorm a 
left join(
select 区域,楼号,宿舍号,count(1)as num 
from stuindorm
group by 区域,楼号,宿舍号) b 
on a.区域=b.区域 and a.楼号=b.楼号 and a.宿舍号=b.宿舍号
where 床位数>isnull(b.num,0)         区域          楼号         宿舍号       剩余床位数
----------- ----------- ----------- -----------
          1           1         101           4
          1           1         102           5
          1           1         103           5
          1           1         104           6
          1           1         105           6(5 行受影响)

Answer 5

--修复历史数据：有
--修改内容:根据改良建议初始化评分机制数据
--中小学、中职校和教育局初始化数据
delete from [GradeStandLevel]
delete from [ElectivePlanItem]
update TermHours set GradeStandID=null 
delete from [GradeStand]
go 
declare @schoolid uniqueidentifier
declare cur cursor for 
select id from department where DepartmentID is null
open cur
Fetch next From Cur into @schoolid
while @@fetch_status=0
Begin 
declare 
     @GradestandID uniqueidentifier 
select
     
     @GradestandID=newid()--评分机制
INSERT INTO [GradeStand]([ID], [Name], [PassScore], [TotalScore],[GradeStandType],[schoolid],IsStand,ScoringCode)
VALUES(newid(), '百分制', 60, 100,0,@schoolid,1,'A001')
INSERT INTO [GradeStand]([ID], [Name], [PassScore], [TotalScore],[GradeStandType],[schoolid],IsStand,ScoringCode)
VALUES(newid(), '120分制', 72, 120,0, @schoolid,1,'A002')
INSERT INTO [GradeStand]([ID], [Name], [PassScore], [TotalScore],[GradeStandType],[schoolid],IsStand,ScoringCode)
VALUES(newid(), '150分制', 90, 150,0, @schoolid,1,'A003')
INSERT INTO [GradeStand]([ID], [Name], [PassScore], [TotalScore],[GradeStandType],[schoolid],IsStand,ScoringCode)
VALUES(@GradestandID, '字母五级评分', 60, 100,1, @schoolid,1,'B001')--相对等级评分法
INSERT INTO [GradeStandLevel]([ID],[LevelName],[Score],[GradeStandID])
VALUES(newid(),'A',90,@GradestandID)
INSERT INTO [GradeStandLevel]([ID],[LevelName],[Score],[GradeStandID])
VALUES(newid(),'B',80,@GradestandID)
INSERT INTO [GradeStandLevel]([ID],[LevelName],[Score],[GradeStandID])
VALUES(newid(),'C',70,@GradestandID)
INSERT INTO [GradeStandLevel]([ID],[LevelName],[Score],[GradeStandID])
VALUES(newid(),'D',60,@GradestandID)
INSERT INTO [GradeStandLevel]([ID],[LevelName],[Score],[GradeStandID])
VALUES(newid(),'E',0,@GradestandID)Fetch Next From Cur Into @schoolid
end
close cur
Deallocate Cur
go
--修复历史数据：有
--修改内容:根据人数比例名称排序
--按人数比例划分档次函数
if   exists(select  *   from   dbo.sysobjects   where   id=object_id(N'dbo.[ProportionView]')   and   objectproperty(id,N'IsInlineFunction')=1)  
drop function [ProportionView]
go
CREATE   FUNCTION   [dbo].[ProportionView](@number int,@schoolid uniqueidentifier)   
  RETURNS   TABLE   
  AS   
  RETURN 
(select C.* from(select A.*,B.Cnt from( SELECT * FROM(SELECT DISTINCT cast(A.ID as varchar(40)) ID,A.SchoolID,A.AreaName,A.StaticType,A.Name,A.IsDeFaultSet FROM 
(select t.ID,t.SchoolID,t.AreaName,[value]=l.listname+':'+cast(l.Proportion as varchar(4))+'-'+cast(l.ImportantFactor as varchar(4)),
t.StaticType,g.Name,t.IsDeFaultSet from 
dbo.ScoreAreaSet_SM as t,
dbo.ScoreAreaSetDetail_SM as l,
GradeStand as g
where l.ScoreAreaID=t.ID
and t.ScoringCode=g.ScoringCode and t.Number=@number and t.SchoolID=@schoolid)as A) A
OUTER APPLY(SELECT 
[values]= STUFF(REPLACE(REPLACE(REPLACE((
SELECT N.value FROM (select t.ID,t.SchoolID,t.AreaName,l.MaxScore,value=l.listname+'：'+cast(l.Proportion as varchar(4))+'%/'+cast(l.ImportantFactor as varchar(4)),
t.StaticType,g.Name,t.IsDeFaultSet,l.ListName from 
dbo.ScoreAreaSet_SM as t,
dbo.ScoreAreaSetDetail_SM as l,
GradeStand as g
where l.ScoreAreaID=t.ID
and t.ScoringCode=g.ScoringCode and t.Number=@number and t.SchoolID=g.SchoolID and t.SchoolID=@schoolid) as N
where N.ID=A.ID order by N.ListName asc
FOR XML AUTO),'N value="', ''), '"/>', ''),'<', '，　　'), 1, 1, '')) as N) as A,(select cast(t.ID as varchar(40)) ID,t.AreaName,count(*) as Cnt,
t.StaticType,g.Name,t.IsDeFaultSet from 
dbo.ScoreAreaSet_SM as t,
dbo.ScoreAreaSetDetail_SM as l,
GradeStand as g
where l.ScoreAreaID=t.ID
and t.ScoringCode=g.ScoringCode
and t.SchoolID=g.SchoolID and t.SchoolID=@schoolid
 group by t.ID,t.AreaName,t.StaticType,g.Name,t.IsDeFaultSet )as B
where A.ID=B.ID) as C)
go
--修复历史数据：有
--修改内容:根据分数排序
--评分机制相对等级视图
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GradestandView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[GradestandView]
go
create view [dbo].[GradestandView] as
select A.* from(SELECT * FROM(SELECT DISTINCT cast(A.ID as varchar(40)) ID,A.SchoolID,A.Name,A.ScoringCode,A.GradeStandType,A.IsStand,A.TotalScore FROM 
(select cast(g.ID as varchar(40)) ID,g.SchoolID,g.Name,g.ScoringCode,g.GradeStandType,g.IsStand,g.TotalScore,[value]=s.LevelName+':'+cast(s.Score as varchar(4))
 from 
gradestand as g,
gradestandlevel as s
where s.GradeStandID=g.ID )as A) A
OUTER APPLY(SELECT 
[values]= STUFF(REPLACE(REPLACE(REPLACE((
SELECT N.value FROM (select cast(g.ID as varchar(40)) ID,g.SchoolID,g.Name,g.ScoringCode,g.GradeStandType,s.Score,g.IsStand,g.TotalScore,[value]=s.LevelName+':'+cast(s.Score as varchar(4))
 from 
gradestand as g,
gradestandlevel as s
where s.GradeStandID=g.ID) as N
WHERE N.ID = A.ID order by N.Score desc 
FOR XML AUTO),'N value="', ''), '"/>', ''),'<', '，　　'), 1, 1, '')) as N) As A 
go
--修复历史数据：有
--修改内容:把基本参数设置菜单改为考试类型信息
update [action] set [name]='考试类型信息' where actionid='4958fa05-af13-49c9-8b03-6bbf358a56c3'
go
--修复历史数据：有
--修改内容:把基本参数设置菜单改为基本参数信息
update [action] set [name]='基本参数信息' where actionid='e7e2c391-e4b1-4dcb-956d-2b8f8f817d65'
go
--修复历史数据：有
--修改内容:把中小学和中职校基本参数信息菜单排在评分机制信息菜单之前
update [action] set showorder=0 where actionid='E7E2C391-E4B1-4DCB-956D-2B8F8F817D65'
go
--修复历史数据：有
--修改内容:把教育局考试类型信息菜单排在标准评分机制信息菜单之前
update [action] set showorder=0 where actionid='4958FA05-AF13-49C9-8B03-6BBF358A56C3'
go
--修复历史数据：有
--修改内容:把教育局公共课程库信息改为标准课程库信息
update [action] set [name]='标准课程库信息' where actionid='defb4310-ea56-4452-b5b0-2f087f406a0f'
go
--修复历史数据：有
--修改内容:把教育局公共评分机制信息改为标准评分机制信息
update [action] set [name]='标准评分机制信息' where actionid='8b22aeb9-dcf3-478e-8cd9-6957f96ff9a8'
goselect convert(varchar(10),month(getdate()))+'-'+convert(varchar(10),day(getdate()))
gostuindorm
学号     区域    楼号   宿舍号
064860247    1    1    101
064860248    1    1    101
064860249    1    1    102
064860250    1    1    103表dorm
区域  楼号 宿舍号 床位数
1    1    101    6
1    1    102    6
1    1    103    6
1    1    104    6
1    1    105    6
create table stuindorm
(
id varchar(20),
area int,
lm int,
dorm varchar(20)
)
insert into stuindorm(id,area,lm,dorm)
(
 select '064860247',1,1,'101' union
 select '064860248',1,1,'101' union
 select '064860249',1,1,'102' union
 select '064860250',1,1,'103' 
)
create table dorm
(
area int,
lm int,
dorm varchar(20),
bed int
)
insert into dorm(area,lm,dorm,bed)
(
 select 1,1,'101',6 union
 select 1,1,'102',6 union
 select 1,1,'103',6 union
 select 1,1,'104',6 union
 select 1,1,'105',6
)1：想查找每个寝室剩余的床位数。。
select d.bed-count(s.lm) from dorm d 
left join stuindorm s on 
d.dorm=s.dorm group by d.dorm,d.bed
go
2：把还有空余床铺的宿舍信息查找出来。。 
select * from(select d.dorm,d.bed-count(s.lm) as [count] from dorm d 
left join stuindorm s on 
d.dorm=s.dorm group by d.dorm,d.bed) as dorm where dorm.[count]>0
go 

Answer 6

1、
Select A.宿舍号,A.床位数-IsNull(B.占床数,0) 剩余床位 From #Dorm A
Left Join
(Select 区域,楼号,宿舍号,Count(学号) 占床数 From #StuInDorm Group By 区域,楼号,宿舍号) B
On A.区域 = B.区域 And A.楼号 = B.楼号 And A.宿舍号 = B.宿舍号
2、
Select A.区域,A.楼号,A.宿舍号,A.床位数-IsNull(B.占床数,0) 空床位数 From #Dorm A
Left Join
(Select 区域,楼号,宿舍号,Count(学号) 占床数 From #StuInDorm Group By 区域,楼号,宿舍号) B
On A.区域 = B.区域 And A.楼号 = B.楼号 And A.宿舍号 = B.宿舍号
Where A.床位数 > IsNull(B.占床数,0)