SQL编程 请教高手

来源：百度知道 编辑：UC知道 时间：2024/06/06 16:09:54
表名 TestRoom
列:RoomAddress
BeginTime (DateTime)
ClientCount 考生数量（0表示没有考生）

例子1：上海 北京
2006-10-10 9:00:00 2006-10-10 9:00:00
20 0

上海 北京
2006-10-11 10:00:00 2006-10-11 10:00:00
0 15

需要显示的效果：
地区 2006-10-10 9:00:00 2006-10-11 10:00:00
上海 1 0
北京 0 1

请问该怎么编写呢？

drop table TestRoom

create table TestRoom
(
RoomAddress varchar(50),
BeginTime DateTime,
ClientCount int
)

insert into TestRoom(RoomAddress,BeginTime,ClientCount) values('上海','2006-10-10 9:00:00',20)
insert into TestRoom(RoomAddress,BeginTime,ClientCount) values('上海','2006-10-11 10:00:00',0)
insert into TestRoom(RoomAddress,BeginTime,ClientCount) values('北京','2006-10-10 9:00:00',0)
insert into TestRoom(RoomAddress,BeginTime,ClientCount) values('北京','2006-10-11 10:00:00',20)

select 地区 ,case when t1 >0 then 1 else 0 end as '2006-10-10 9:00:00'
,case when t2>0 then 1 else 0 end as '2006-10-11 9:00:00'
from(
select RoomAddress as '地区',
sum(case BeginTime when '2006-10-10 9:00:00' then ClientCount else 0 end ) as 't1',
sum(case B