mysql列转行和行转列

列转行：

先看未转换前的查询结果：假设这条查询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合并结果集