求一统计的SQL

Question

表:
room name inc date
101   aa   11  2008-03-01
101   bb   12  2008-03-01
101   cc   13  2008-03-01
102   dd   11  2008-03-01
101   aa   21  2008-04-01
101   bb   22  2008-04-01
101   cc   23  2008-04-01
102   ee   21  2008-04-01
101   aa   11  2008-05-01
101   bb   12  2008-05-01
101   cc   13  2008-05-01每月的记录数可能不同,只统计两个月都有的name.结果
room name 200803 200804
101   aa   11     21
101   bb   12     22
101   cc   13     33

Answer 1

只统计两个月都有的name. 
---------------------只统计三月和四月?

Answer 2

create table tb(room int , name varchar(10) , inc int , date datetime)
insert into tb values(101 ,  'aa' ,  11 , '2008-03-01') 
insert into tb values(101 ,  'bb' ,  12 , '2008-03-01') 
insert into tb values(101 ,  'cc' ,  13 , '2008-03-01') 
insert into tb values(102 ,  'dd' ,  11 , '2008-03-01') 
insert into tb values(101 ,  'aa' ,  21 , '2008-04-01') 
insert into tb values(101 ,  'bb' ,  22 , '2008-04-01') 
insert into tb values(101 ,  'cc' ,  23 , '2008-04-01') 
insert into tb values(102 ,  'ee' ,  21 , '2008-04-01') 
insert into tb values(101 ,  'aa' ,  11 , '2008-05-01') 
insert into tb values(101 ,  'bb' ,  12 , '2008-05-01') 
insert into tb values(101 ,  'cc' ,  13 , '2008-05-01')
goselect m.room , n.name ,
max(case convert(varchar(7), date , 120) when '2008-03' then inc else 0 end) [200803],
max(case convert(varchar(7), date , 120) when '2008-04' then inc else 0 end) [200804]
from tb m,(select room , name from tb where convert(varchar(7), date , 120) between '2008-03' and '2008-04' group by room , name having count(*) = 2) n
where m.room = n.room and m.name = n.name
group by m.room , n.namedrop table tb/*
room        name       200803      200804      
----------- ---------- ----------- ----------- 
101         aa         11          21
101         bb         12          22
101         cc         13          23（所影响的行数为 3 行）
*/

Answer 3

create table tb(room int,name varchar(10),inc int,date datetime)
insert into tb select 101,'aa',11,'2008-03-01'
insert into tb select 101,'aa',21,'2008-04-01'
insert into tb select 101,'bb',12,'2008-03-01'
insert into tb select 101,'bb',22,'2008-04-01'
insert into tb select 101,'cc',13,'2008-03-01'
insert into tb select 101,'cc',23,'2008-04-01'
insert into tb select 101,'dd',11,'2008-03-01'
insert into tb select 101,'dd',21,'2008-04-01'
insert into tb select 101,'dd',23,'2008-05-01'
insert into tb select 102,'ee',21,'2008-04-01'declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case convert(varchar(7), date , 120) when '''+date+''' then inc else 0 end) ['+date+']'
from (select distinct convert(varchar(7), date , 120) as date from (select * from tb t where exists(
select 1 from tb where name=t.name 
and convert(varchar(7),date,120)<>convert(varchar(7),t.date,120)))t)tp
exec('select room,name,'+@sql+'from tb t
where exists(
select 1 from tb where name=t.name 
and convert(varchar(7),date,120)<>convert(varchar(7),t.date,120)
) group by room,name')room name 2008-03 2008-04 2008-05
101 aa 11 21 0
101 bb 12 22 0
101 cc 13 23 0
101 dd 11 21 23

Answer 4

if object_id('tb') is not null
   drop table tb
go
create table tb(room int,name varchar(10),inc int,date datetime)
insert into tb select 101,'aa',11,'2008-03-01'
insert into tb select 101,'bb',12,'2008-03-01'
insert into tb select 101,'cc',13,'2008-03-01'
insert into tb select 102,'dd',11,'2008-03-01'
insert into tb select 101,'aa',21,'2008-04-01'
insert into tb select 101,'bb',22,'2008-04-01'
insert into tb select 101,'cc',23,'2008-04-01'
insert into tb select 102,'ee',21,'2008-04-01'
insert into tb select 101,'aa',11,'2008-05-01'
insert into tb select 101,'bb',12,'2008-05-01'
insert into tb select 101,'cc',13,'2008-05-01'select * from tb
go
if object_id('usp_test') is not null
   drop proc usp_test
go
--usp_test '2008-03-01 00:00:00.000','2008-04-01 00:00:00.000'
create proc usp_test(@date_1 datetime,@date_2 datetime)
as
begin
   
   select distinct name into #1 from tb where date=@date_1 
     and name in (select  distinct name from tb where date=@date_2)
   declare @sql varchar(8000)
   set @sql='select name,'
   select @sql=@sql+'max(case when date='''+cast(@date_1 as varchar(10))+''' then inc else 0 end) as ['+convert(varchar(4),@date_1,120)+right('0'+cast(datepart(mm,@date_1) as varchar(2)),2)+'],'
   select @sql=@sql+'max(case when date='''+cast(@date_2 as varchar(10))+''' then inc else 0 end) as ['+convert(varchar(4),@date_2,120)+right('0'+cast(datepart(mm,@date_2) as varchar(2)),2)+'],'
   set @sql=substring(@sql,1,len(@sql)-1)
   set @sql=' from (select * from tb where (date='''+cast(@date_1 as varchar(100))+''' or date='''+cast(@date_2 as varchar(100))+''') and name in (select * from #1) ) a '+'group by name' 
   exec(@sql)
   print @sql
   drop table #1
end

Answer 5

select * from (select room , name ,
max(case convert(varchar(7), date , 120) when '2008-03' then inc else 0 end) [200803],
max(case convert(varchar(7), date , 120) when '2008-04' then inc else 0 end) [200804]
from tb group by room ,name) t where [200803]<>'0' and [200804]>'0'