呜呜，求一条SQL语句，
Date Priority
2006-10-072
2006-10-072
2006-10-131
2006-11-072
2006-11-082
2006-11-223
2006-12-192
2007-01-092
2007-01-093
2007-01-102
2007-01-121
2007-01-161
其中Priority的取值只能为1，2，3
最后得到每天的Priority个数，
如下结果：
Date P1 P2 P3
2006-10-07 0 2 0
2006-10-13 1 0 0
2006-11-07 0 1 0
...........
[解决办法]
select Date,count(case Priority when 1 then Priority end)[p1],
count(case Priority when 2 then Priority end)[p2],
count(case Priority when 3 then Priority end)[p3]
from tab group by Date
[解决办法]
--上面gahade(与君共勉)兄是在你Priority只有1,2,3的情况下的静态SQL
--下面是动态SQL
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(Date varchar(10),Priority int)
insert into tb(Date,Priority) values( '2006-10-07 ',2)
insert into tb(Date,Priority) values( '2006-10-07 ',2)
insert into tb(Date,Priority) values( '2006-10-13 ',1)
insert into tb(Date,Priority) values( '2006-11-07 ',2)
insert into tb(Date,Priority) values( '2006-11-08 ',2)
insert into tb(Date,Priority) values( '2006-11-22 ',3)
insert into tb(Date,Priority) values( '2006-12-19 ',2)
insert into tb(Date,Priority) values( '2007-01-09 ',2)
insert into tb(Date,Priority) values( '2007-01-09 ',3)
insert into tb(Date,Priority) values( '2007-01-10 ',2)
insert into tb(Date,Priority) values( '2007-01-12 ',1)
insert into tb(Date,Priority) values( '2007-01-16 ',1)
go
declare @sql varchar(8000)
set @sql = 'select Date '
select @sql = @sql + ' , sum(case Priority when ' ' ' + cast(Priority as varchar) + ' ' ' then 1 else 0 end) [ ' + 'P ' + cast(Priority as varchar) + '] '
from (select distinct Priority from tb) as a
set @sql = @sql + ' from tb group by Date '
exec(@sql)
drop table tb
/*
Date P1 P2 P3
---------- ----------- ----------- -----------
2006-10-07 0 2 0
2006-10-13 1 0 0
2006-11-07 0 1 0
2006-11-08 0 1 0
2006-11-22 0 0 1
2006-12-19 0 1 0
2007-01-09 0 1 1
2007-01-10 0 1 0
2007-01-12 1 0 0
2007-01-16 1 0 0
&/
