【mysql练习】转置,总计,分组

 

1，有如下一个表，要求把这个表按照2016，2017，2018，2019，把从1月到12月的金额展示出来。

profit表：

year month amount
2019 1 5
2019 3 6
2018 3 7
2018 4 8
2018 5 9
2017 1 10
2017 2 11
2016 3 12

展现成：
year，1，2，3，4，5，6，7，8，9，10，11，12

2016，0，0，12，0，0，0，0，0，0，0，0，0

2017，10，11，0，0，0，0，0，0，0，0，0，0

2018，0，0，7，8，9，0，0，0，0，0，0，0

2019，5，0，6，0，0，0，0，0，0，0，0，0

 

 

解析：由于要展示1-12月的数据，所以先把1-12月的列列出来。每列的值都由查询对应月份的结果进行展示，4年的结果分组展示。

第一步，写出每个月的查询结果：

select amount as "1月" from profit where month="1";
select amount as "2月" from profit where month="2";
select amount as "3月" from profit where month="3";
select amount as "4月" from profit where month="4";
select amount as "5月" from profit where month="5";
select amount as "6月" from profit where month="6";
select amount as "7月" from profit where month="7";
select amount as "8月" from profit where month="8";
select amount as "9月" from profit where month="9";
select amount as "10月" from profit where month="10";
select amount as "11月" from profit where month="11";
select amount as "12月" from profit where month="12";

第二步：

将每个月的查询结果作为子查询，通过year关联

select year,
(select amount from profit m1 where m1.month="1" and m1.year=m.year) as m1,
(select amount from profit m2 where m2.month="2" and m2.year=m.year) as m2,
(select amount from profit m3 where m3.month="3" and m3.year=m.year) as m3,
(select amount from profit m4 where m4.month="4" and m4.year=m.year) as m4,
(select amount from profit m5 where m5.month="5" and m5.year=m.year) as m5,
(select amount from profit m6 where m6.month="6" and m6.year=m.year) as m6,
(select amount from profit m7 where m7.month="7" and m7.year=m.year) as m7,
(select amount from profit m8 where m8.month="8" and m8.year=m.year) as m8,
(select amount from profit m9 where m9.month="9" and m9.year=m.year) as m9,
(select amount from profit m10 where m10.month="10" and m10.year=m.year) as m10,
(select amount from profit m11 where m11.month="11" and m11.year=m.year) as m11,
(select amount from profit m12 where m12.month="12" and m12.year=m.year) as m12
from profit m group by m.year

第三步，处理Null

select year,
IFNULL((select amount from profit m1 where m1.month="1" and m1.year=m.year),0) as m1,
IFNULL((select amount from profit m2 where m2.month="2" and m2.year=m.year),0) as m2,
IFNULL((select amount from profit m3 where m3.month="3" and m3.year=m.year),0) as m3,
IFNULL((select amount from profit m4 where m4.month="4" and m4.year=m.year),0) as m4,
IFNULL((select amount from profit m5 where m5.month="5" and m5.year=m.year),0) as m5,
IFNULL((select amount from profit m6 where m6.month="6" and m6.year=m.year),0) as m6,
IFNULL((select amount from profit m7 where m7.month="7" and m7.year=m.year),0) as m7,
IFNULL((select amount from profit m8 where m8.month="8" and m8.year=m.year),0) as m8,
IFNULL((select amount from profit m9 where m9.month="9" and m9.year=m.year),0) as m9,
IFNULL((select amount from profit m10 where m10.month="10" and m10.year=m.year),0) as m10,
IFNULL((select amount from profit m11 where m11.month="11" and m11.year=m.year),0) as m11,
IFNULL((select amount from profit m12 where m12.month="12" and m12.year=m.year),0) as m12
from profit m group by m.year

 

结果：

 

增加统计当年amount

select year,sum(amount) as "当年总金额",
IFNULL((select amount from profit m1 where m1.month="1" and m1.year=m.year),0) as m1,
IFNULL((select amount from profit m2 where m2.month="2" and m2.year=m.year),0) as m2,
IFNULL((select amount from profit m3 where m3.month="3" and m3.year=m.year),0) as m3,
IFNULL((select amount from profit m4 where m4.month="4" and m4.year=m.year),0) as m4,
IFNULL((select amount from profit m5 where m5.month="5" and m5.year=m.year),0) as m5,
IFNULL((select amount from profit m6 where m6.month="6" and m6.year=m.year),0) as m6,
IFNULL((select amount from profit m7 where m7.month="7" and m7.year=m.year),0) as m7,
IFNULL((select amount from profit m8 where m8.month="8" and m8.year=m.year),0) as m8,
IFNULL((select amount from profit m9 where m9.month="9" and m9.year=m.year),0) as m9,
IFNULL((select amount from profit m10 where m10.month="10" and m10.year=m.year),0) as m10,
IFNULL((select amount from profit m11 where m11.month="11" and m11.year=m.year),0) as m11,
IFNULL((select amount from profit m12 where m12.month="12" and m12.year=m.year),0) as m12
from profit m group by m.year;

 

 

 增加统计4年总金额：

select year,sum(amount) as "年度总金额",
IFNULL((select amount from profit m1 where m1.month="1" and m1.year=m.year),0) as m1,
IFNULL((select amount from profit m2 where m2.month="2" and m2.year=m.year),0) as m2,
IFNULL((select amount from profit m3 where m3.month="3" and m3.year=m.year),0) as m3,
IFNULL((select amount from profit m4 where m4.month="4" and m4.year=m.year),0) as m4,
IFNULL((select amount from profit m5 where m5.month="5" and m5.year=m.year),0) as m5,
IFNULL((select amount from profit m6 where m6.month="6" and m6.year=m.year),0) as m6,
IFNULL((select amount from profit m7 where m7.month="7" and m7.year=m.year),0) as m7,
IFNULL((select amount from profit m8 where m8.month="8" and m8.year=m.year),0) as m8,
IFNULL((select amount from profit m9 where m9.month="9" and m9.year=m.year),0) as m9,
IFNULL((select amount from profit m10 where m10.month="10" and m10.year=m.year),0) as m10,
IFNULL((select amount from profit m11 where m11.month="11" and m11.year=m.year),0) as m11,
IFNULL((select amount from profit m12 where m12.month="12" and m12.year=m.year),0) as m12
from profit m group by m.year
UNION ALL
select * from
(select '总计',sum(t2.`年度总金额`),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),sum(m8),sum(m9),sum(m10),sum(m11),sum(m12) from
(
select year,sum(amount) as "年度总金额",
IFNULL((select amount from profit m1 where m1.month="1" and m1.year=m.year),0) as m1,
IFNULL((select amount from profit m2 where m2.month="2" and m2.year=m.year),0) as m2,
IFNULL((select amount from profit m3 where m3.month="3" and m3.year=m.year),0) as m3,
IFNULL((select amount from profit m4 where m4.month="4" and m4.year=m.year),0) as m4,
IFNULL((select amount from profit m5 where m5.month="5" and m5.year=m.year),0) as m5,
IFNULL((select amount from profit m6 where m6.month="6" and m6.year=m.year),0) as m6,
IFNULL((select amount from profit m7 where m7.month="7" and m7.year=m.year),0) as m7,
IFNULL((select amount from profit m8 where m8.month="8" and m8.year=m.year),0) as m8,
IFNULL((select amount from profit m9 where m9.month="9" and m9.year=m.year),0) as m9,
IFNULL((select amount from profit m10 where m10.month="10" and m10.year=m.year),0) as m10,
IFNULL((select amount from profit m11 where m11.month="11" and m11.year=m.year),0) as m11,
IFNULL((select amount from profit m12 where m12.month="12" and m12.year=m.year),0) as m12
from profit m group by m.year
) t2 ) t3

 

改进版：

select IFNULL(year,"总计") as year,sum(t.total) as 'total' ,sum(t.m1) as '1月',
sum(t.m2) as '2月',
sum(t.m3) as '3月',
sum(t.m4) as '4月',
sum(t.m5) as '5月',
sum(t.m6) as '6月',
sum(t.m7) as '7月',
sum(t.m8) as '8月',
sum(t.m9) as '9月',
sum(t.m10) as '10月',
sum(t.m11) as '11月',
sum(t.m12) as '12月'from
(
select year,sum(amount) as 'total',
IFNULL((select amount from profit m1 where m1.month="1" and m1.year=m.year),0) as m1,
IFNULL((select amount from profit m2 where m2.month="2" and m2.year=m.year),0) as m2,
IFNULL((select amount from profit m3 where m3.month="3" and m3.year=m.year),0) as m3,
IFNULL((select amount from profit m4 where m4.month="4" and m4.year=m.year),0) as m4,
IFNULL((select amount from profit m5 where m5.month="5" and m5.year=m.year),0) as m5,
IFNULL((select amount from profit m6 where m6.month="6" and m6.year=m.year),0) as m6,
IFNULL((select amount from profit m7 where m7.month="7" and m7.year=m.year),0) as m7,
IFNULL((select amount from profit m8 where m8.month="8" and m8.year=m.year),0) as m8,
IFNULL((select amount from profit m9 where m9.month="9" and m9.year=m.year),0) as m9,
IFNULL((select amount from profit m10 where m10.month="10" and m10.year=m.year),0) as m10,
IFNULL((select amount from profit m11 where m11.month="11" and m11.year=m.year),0) as m11,
IFNULL((select amount from profit m12 where m12.month="12" and m12.year=m.year),0) as m12
from profit m group by m.year
) t
group by year with rollup;