呜呜，求一条SQL语句，

Question

Date           Priority
2006-10-07 2
2006-10-07 2
2006-10-13 1
2006-11-07 2
2006-11-08 2
2006-11-22 3
2006-12-19 2
2007-01-09 2
2007-01-09 3
2007-01-10 2
2007-01-12 1
2007-01-16 1其中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
...........

Answer 1

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

Answer 2

select date,
sum(case when p1=1 then 1 else 0 end) as P1,
sum(case when p1=2 then 1 else 0 end) as P2,
sum(case when p1=3 then 1 else 0 end) as P3
from 表
group by Date

Answer 3

--上面gahade(与君共勉)兄是在你Priority只有1,2,3的情况下的静态SQL
--下面是动态SQLif object_id('pubs..tb') is not null
   drop table tb
gocreate 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)
godeclare @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
&/

Answer 4

--gahade(与君共勉) 把字段名搞错了.select date,
sum(case when Priority=1 then 1 else 0 end) as P1,
sum(case when Priority=2 then 1 else 0 end) as P2,
sum(case when Priority=3 then 1 else 0 end) as P3
from tb
group by Date/*
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（所影响的行数为 10 行）*/

Answer 5

其中Priority的取值只能为1，2，3

Answer 6

借用楼上的例子:
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)declare @sql varchar(4000)
set @sql=''
select @sql=@sql+','+quotename('P'+rtrim(Priority))+'=sum(case Priority when '+rtrim(Priority)+' then Priority else 0 end)'
from tb group by Priority
exec('select Date'+@sql+' from tb group by Date')
Date       P1          P2          P3
---------- ----------- ----------- -----------
2006-10-07 0           4           0
2006-10-13 1           0           0
2006-11-07 0           2           0
2006-11-08 0           2           0
2006-11-22 0           0           3
2006-12-19 0           2           0
2007-01-09 0           2           3
2007-01-10 0           2           0
2007-01-12 1           0           0
2007-01-16 1           0           0(10 行受影响)

Answer 7

declare @sql varchar(4000)
set @sql=''
select @sql=@sql+','+quotename('P'+rtrim(Priority))+'=sum(case Priority when '+rtrim(Priority)+' then 1 else 0 end)'--把Priority改为1
from tb group by Priority
exec('select Date'+@sql+' from tb group by Date')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(10 行受影响)