数据表:
列转行:利用max(case when then)
合并字段显示:利用graup_cancat(name1,”;”,”name2”)
-- SELECT * from 20160413CaiDi WHERE 1=1 limit 0,5;
-- 列转行
select year,
(CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
(CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
(CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
(CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4
from 20160413CaiDi;
GROUP BY year;
-- 列转行
select year,
(CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
(CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
(CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
(CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4
from 20160413CaiDi;
GROUP BY year;
select year,
MAX(CASE `month` WHEN 1 THEN money ELSE 0 END ) as m1,
MAX(CASE `month` WHEN 2 THEN money ELSE 0 END ) as m2,
MAX(CASE `month` WHEN 3 THEN money ELSE 0 END ) as m3,
MAX(CASE `month` WHEN 4 THEN money ELSE 0 END ) as m4
from 20160413CaiDi
GROUP BY year;
-- 列转行
select year,
MAX(CASE `month` WHEN "1" THEN money ELSE null END ) as m1,
MAX(CASE `month` WHEN "2" THEN money ELSE null END ) as m2,
MAX(CASE `month` WHEN "3" THEN money ELSE null END ) as m3,
MAX(CASE `month` WHEN "4" THEN money ELSE null END ) as m4
from 20160413CaiDi
GROUP BY year;
-- 行专列(合并字段)
select year,GROUP_CONCAT(month,":",money) as appendWord
from 20160413CaiDi GROUP BY year,month;
select year,month,GROUP_CONCAT("money is:",money) as appendWord
from 20160413CaiDi GROUP BY year;