求助：按状态统计的sql

[cqq]

一个图片表， 用户都可以上传图片，图片有4种状态，0 新上传 1 提交审核 2 审核通过 3 未通过

我想按天、状态统计，得到这样的结果

日期 新上传 提交审核 通过 未通过
2010-8-30 2张 100张
2010-8-31



这样的结果，sql语句该怎么写？
谢谢

[jwdream2008]

select convert(nvarchar(10),getdate(),120) as '日期'
      ,sum(case when [状态]=0 then 1 else 0 end) as '新上传'
      ,sum(case when [状态]=1 then 1 else 0 end) as '提交审核'
      ,sum(case when [状态]=2 then 1 else 0 end) as '审核通过'
      ,sum(case when [状态]=3 then 1 else 0 end) as '未通过'
from Table1
group by convert(nvarchar(10),getdate(),120)

[hao1hao2hao3]

[Quote=引用 1 楼 jwdream2008 的回复:]
SQL code
select convert(nvarchar(10),getdate(),120) as '日期'
,sum(case when [状态]=0 then 1 else 0 end) as '新上传'
,sum(case when [状态]=1 then 1 else 0 end) as '提交审核'
,sum(case when [状……
[/Quote]

支持！

select convert(nvarchar(10),日期,120) as '日期'
      ,cast(sum(case when [状态]=0 then 1 else 0 end) as varchar(10))+'张' as '新上传'
      ,cast(sum(case when [状态]=1 then 1 else 0 end) as varchar(10))+'张' as '提交审核'
      ,cast(sum(case when [状态]=2 then 1 else 0 end) as varchar(10))+'张'as '审核通过'
      ,cast(sum(case when [状态]=3 then 1 else 0 end) as varchar(10))+'张' as '未通过'
from Table1
group by convert(nvarchar(10),日期,120)

[Rotel-刘志东]

select convert(nvarchar(20),getdate(),120) as'日期'
,sum(case when [状态]=0 then 0 else 0 end) as'新上传'
,sum(case when [状态]=1 then 1 else 0 end) as '提交审核'
,sum(case when [状态]=2 then 2 else 0 end) as '审核通过'
,sum(case when [状态]=3 then 3 else 0 end) as'未通过了'
from tb 
group by convert(nvarchar(20),getdate(),120)

[「已注销」]

select convert(nvarchar(10),日期,120) as '日期'
      ,cast(sum(case when [状态]=0 then 1 else 0 end) as varchar(10))+'张' as '新上传'
      ,cast(sum(case when [状态]=1 then 1 else 0 end) as varchar(10))+'张' as '提交审核'
      ,cast(sum(case when [状态]=2 then 1 else 0 end) as varchar(10))+'张'as '审核通过'
      ,cast(sum(case when [状态]=3 then 1 else 0 end) as varchar(10))+'张' as '未通过'
from Table1
group by convert(nvarchar(10),日期,120)

支持~