数据表 STU
ID TIME NOU NOY
1 2012-1-4 2 52 2012-1-4 7 23 2012-1-8 5 64 2012-1-18 9 15 2012-1-28 6 6怎么查出 NOU和NOY一个月每天的数量 这样的结果
NAME 1 2 3 4 ....8 9 10....18.......28 29 30 31
NOU 9 5 9 6
NOY 9 5 9 6谢谢
ID TIME NOU NOY
1 2012-1-4 2 52 2012-1-4 7 23 2012-1-8 5 64 2012-1-18 9 15 2012-1-28 6 6怎么查出 NOU和NOY一个月每天的数量 这样的结果
NAME 1 2 3 4 ....8 9 10....18.......28 29 30 31
NOU 9 5 9 6
NOY 9 5 9 6谢谢
sum(case when day(TIME)=1 then NOU else 0 end) as [1],
sum(case when day(TIME)=2 then NOU else 0 end) as [2],
......
sum(case when day(TIME)=31 then NOU else 0 end) as [31]
from STU
union all
select 'NOY' as NAME,
sum(case when day(TIME)=1 then NOY else 0 end) as [1],
sum(case when day(TIME)=2 then NOY else 0 end) as [2],
......
sum(case when day(TIME)=31 then NOY else 0 end) as [31]
from STU
再通过SQL2005的行列转行即可