救急，SQL查询！！！
表：
year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
要求查询出这样一种结果：
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
希望有各种数据库版本的查询语句
[解决办法]
SELECT year,SUM(CASE WHEN month=1 THEN amount ELSE 0 END ) AS M1,
SUM(CASE WHEN month=2 THEN amount ELSE 0 END ) AS M2,
SUM(CASE WHEN month=3 THEN amount ELSE 0 END ) AS M3,
SUM(CASE WHEN month=4 THEN amount ELSE 0 END ) AS M4
FROM TT GROUP BY year
MYSQL :
CASE WHEN->IF
SELECT year,SUM(IF(month=1,amount,0)) AS M1,...SUM(IF(month=N,amount,0)) AS MN
FROM TT GROUP BY year
[解决办法]
动态的参考
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?17482
中我的回答
SQLSERVER思路是相同的
[解决办法]
参考下贴中的多种方法
http://blog.csdn.net/acmain_chm/article/details/4283943
[解决办法]
--以下可用于sql server , oracle
select year,
max(case when month = 1 then amount else 0 end) m1,
max(case when month = 2 then amount else 0 end) m2,
max(case when month = 3 then amount else 0 end) m3,
max(case when month = 4 then amount else 0 end) m4
from tb
group by year
--以下可用于oracle
select year,
max(decode(month , 1 , amount , 0)) m1,
max(decode(month , 2 , amount , 0)) m2,
max(decode(month , 3 , amount , 0)) m3,
max(decode(month , 4 , amount , 0)) m4
from tb
group by year
