sql语句问题
有表kk
日期 name num
2003-01-01 w1 1
2003-01-02 w2 1
2003-01-03 w3 1
咋样用sql语句查出这样的结果（最好用case实现)
号数 w1 w2 w3
1 1
2 1
3 1
问题点数：0、回复次数：7Top
1 楼CSDNM（决定不当CSDN经理了）回复于 2003-08-03 09:42:41 得分 0
SELECT DATEPART(DAY,日期) AS 号数,
SUM(CASE WHEN NAME='w1' THEN num ELSE 0 END ) AS W1,
SUM(CASE WHEN NAME='w2' THEN num ELSE 0 END ) AS W2,
SUM(CASE WHEN NAME='w3' THEN num ELSE 0 END ) AS W3
FROM KK
GROUP BY DATEPART(DAY,日期)
Top
2 楼norlights（烟火）回复于 2003-08-03 09:50:36 得分 0
我 表里面不是就 三行呀有很 多行Top
3 楼pengdali（）回复于 2003-08-03 10:31:19 得分 0
declare @sql varchar(8000)
set @sql = 'select day(日期)'
select @sql = @sql + ',case name when '''+name+''' then num end ['+name+']'
from (select distinct name from kk) as a
select @sql = @sql+' from kk'
exec(@sql)
go
Top
4 楼pengdali（）回复于 2003-08-03 10:33:45 得分 0
declare @sql varchar(8000)
set @sql = 'select day(日期) 号数'
select @sql = @sql + ',sum(case name when '''+name+''' then num end) ['+name+']'
from (select distinct name from kk) a
select @sql = @sql+' from kk group by day(日期)'
exec(@sql)
go
Top
5 楼qianguob（不懂编程）回复于 2003-08-03 13:16:24 得分 0
对了，这样肯定可以了。Top
6 楼CrazyFor（冬眠的鼹鼠）回复于 2003-08-03 13:50:58 得分 0
赞同大力的。：）Top
7 楼zjcxc（邹建）回复于 2003-08-03 14:19:28 得分 0
name不固定要用动态生成SQL的方法
declare @sql varchar(8000)
set @sql='select day(日期) as 号数'
select @sql=@sql+',case name when '''+name+''' then num else '''' end as '+name
from (select distince name from kk) a
set @sql=@sql+' from kk group by day(日期)'
exec(@sql)Top
