列转行:
先看未转换前的查询结果:假设这条查询sql为 String sql = s;
期望结果:
转换语句:
方案1:
SELECT organization, SUM( IF ( month = '4', num, 0 ) ) AS '4月',
SUM( IF ( month = '5', num, 0 ) ) AS '5月',
SUM( IF ( month = '6', num, 0 ) ) AS '6月',
SUM( IF ( month = '7', num, 0 ) ) AS '7月',
SUM( IF ( month = '8', num, 0 ) ) AS '8月',
SUM( IF ( month = '9', num, 0 ) ) AS '9月'
FROM (
s
) tt GROUP BY organization
方案2:
SELECT organization, Max(CASE WHEN `month` = 4 THEN num ELSE 0 END) as "4月",
Max(CASE WHEN `month` = 5 THEN num ELSE 0 END) as "5月",
Max(CASE WHEN `month` = 6 THEN num ELSE 0 END) as "6月",
Max(CASE WHEN `month` = 7 THEN num ELSE 0 END) as "7月",
Max(CASE WHEN `month` = 8 THEN num ELSE 0 END) as "8月",
Max(CASE WHEN `month` = 9 THEN num ELSE 0 END) as "9月"
FROM (
s
) tt GROUP BY organization
行转列:假设上一条的查询语句为 string sql = a;
SELECT organization, "4月" as `month`, 4月 number from(
a
)x where x.4月 > 0
UNION
SELECT organization, "5月" as `month`, 5月 number from(
a
)x where x.5月 > 0
......
结果:
- 行转列,通常是需要将信息汇总展示,每行展示更多更详细的信息,所以会用到group by分组函数
- 列转行,通常是需要将信息拆分后充足,每行展示的信息减少,但是总行数会增加,通常会用到union合并结果集