Mysql 交叉查询

CREATE TABLE `taa` (
   `year` varchar(4) DEFAULT NULL,
   `month` varchar(2) DEFAULT NULL,
   `amount` double DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf
"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
1.select  a.year,a.m1,a.m2,b.m3,b.m4 from
(select year,
sum(if(month=1,amount,0)) as m1,
sum(if(MONTH=2,amount,0)) AS m2
from taa
group by year) a,
(SELECT year,
SUM(IF(MONTH=3,amount,0)) AS m3,
SUM(IF(MONTH=4,amount,0)) AS m4
from taa
GROUP BY YEAR) b
where a.year=b.year;
2.select year,
max((case month when 1 then amount end)) as m1,
max((CASE MONTH WHEN 2 THEN amount END)) AS m2,
max((CASE MONTH WHEN 3 THEN amount END)) AS m3,
max((CASE MONTH WHEN 4 THEN amount END)) AS m4
from taa
group by year;