sql统计的语句

[cqq]

photo表结构
photoid title status confirmdate
1 图片1 0 2011-06-14
2 图片2 0 2011-06-16
3 图片3 1 2011-06-16


需求是，按日期统计，每天的不同状态的图片有多少个

结果是这样子的

confirmdate status0 status1
2011-6-14 1 0
2011-6-16 1 1

[GoAwayZ]

select confirmdate=convert(varchar(10),confirmdate,120),
        status0=sum(case when status=0 then 1 else 0 end),
        status1=sum(case when status=1 then 1 else 0 end)
from photo group by convert(varchar(10),confirmdate,120),

[cd731107]

select confirmdate,sum(case when status=0 then 1 else 0 end) as status0,
       sum(case when status=1 then 1 else 0 end) as status1
from photo
group by confirmdate

[挨踢直男]

select convert(char(10),confirmdate,120) 日期,
count(case status when 0 then 1 else null end) status0,
count(case status when 1 then 1 else null end) status1
from photo group by convert(char(10),confirmdate,120)

[东那个升]

create table test(photoid int,title varchar(20),status  int,confirmdate datetime)

insert test select 1 ,'图片1', 0 ,'2011-06-14'
insert test select 2 ,'图片2', 0 ,'2011-06-16'
insert test select 3 ,'图片3', 1 ,'2011-06-16'



declare @sql varchar(8000)
set @sql = 'select confirmdate '
select @sql = @sql + ' , max(case status when ''' + ltrim(status) + ''' then num else 0 end) [ststus' + ltrim(status) + ']'
from (select status from test group by status) as a
set @sql = @sql + ' from (select confirmdate,status,count(1) as num from test
 group by confirmdate,status) a group by confirmdate'
exec(@sql) 

confirmdate             ststus0     ststus1
----------------------- ----------- -----------
2011-06-14 00:00:00.000 1           0
2011-06-16 00:00:00.000 1           1

(2 行受影响)

[东那个升]

declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + ltrim(status) from test group by status
set @sql = '[' + @sql + ']'
exec ('select * from (select confirmdate,status,count(1) as num from test
 group by confirmdate,status) a pivot (max(num) for status in (' + @sql + ')) b')

confirmdate             0           1
----------------------- ----------- -----------
2011-06-14 00:00:00.000 1           NULL
2011-06-16 00:00:00.000 1           1

(2 行受影响)