数据库行转列查询例子

select * from t_test

id year month chanliang
1 1991 1 200
2 1991 2 80
3 1991 3 150
4 1992 1 245
5 1992 2 100
6 1992 3 20
7 1993 1 100
8 1993 2 200
9 1993 3 300

显示为： 年份 一月 二月 三月
1991 200 80 150
1992 150 245 20
1993 100 200 300

*/

select t1.year
,(select t2.produce_num from t_produce t2 where t2.month=1 and t2.year=t1.year) as ‘一月’
,(select t2.produce_num from t_produce t2 where t2.month=2 and t2.year=t1.year) as ‘二月’
,(select t2.produce_num from t_produce t2 where t2.month=3 and t2.year=t1.year) as ‘三月’
from t_produce as t1 group by year;

select t1.year,
sum(case when t1.month=1 then t1.produce_num else 0 end),
sum(case when t1.month=2 then t1.produce_num else 0 end),
sum(case when t1.month=3 then t1.produce_num else 0 end)
from t_produce as t1 group by year;