mysql行转列

最新推荐文章于 2024-02-29 17:53:31 发布

SsiGan

最新推荐文章于 2024-02-29 17:53:31 发布

阅读量685

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/SsiGan/article/details/126586157

版权

首先，mysql不支持pivot和unpivot，所以只能手动转换

有两种方法，推荐方法二。

方法一如下：

SELECT
	t.wellNum,
	SUM(t.water01) AS water01,
	SUM(t.water02) AS water02,
	SUM(t.water03) AS water03,
	SUM(t.water04) AS water04,
	SUM(t.water05) AS water05,
	SUM(t.water06) AS water06,
	SUM(t.water07) AS water07,
	SUM(t.water08) AS water08,
	SUM(t.water09) AS water09,
	SUM(t.water10) AS water10,
	SUM(t.water11) AS water11,
	SUM(t.water12) AS water12
FROM
	(
	SELECT
		c.wellCode AS wellNum,
	CASE
			
			WHEN SUBSTR( c.yearMonth, 6, 2 ) = '01' THEN
			c.useWater 
		END water01,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '02' THEN
		c.useWater 
	END water02,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '03' THEN
		c.useWater 
	END water03,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '04' THEN
		c.useWater 
	END water04,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '05' THEN
		c.useWater 
	END water05,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '06' THEN
		c.useWater 
	END water06,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '07' THEN
		c.useWater 
	END water07,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '08' THEN
		c.useWater 
	END water08,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '09' THEN
		c.useWater 
	END water09,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '10' THEN
		c.useWater 
	END water10,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '11' THEN
		c.useWater 
	END water11,
CASE
		
		WHEN SUBSTR( c.yearMonth, 6, 2 ) = '12' THEN
		c.useWater 
	END water12 
FROM
	calc_water_month c
	LEFT JOIN t_s_territory t ON c.territoryCode = t.territorycode
	LEFT JOIN t_s_role_datafunction td ON td.functionid = t.ID 
WHERE
	td.roleid = '2c919ca7810b53d5018112db65dd0150' 
	AND SUBSTR( c.yearMonth, 1, 4 ) = '2022' 
GROUP BY
	c.wellCode,
	SUBSTR( c.yearMonth, 6, 2 ) 
ORDER BY
	c.wellCode,
	c.yearMonth 
	) t
GROUP BY t.wellNum
ORDER BY t.wellNum;

相对很复杂。

方法二如下：

SELECT
	c.wellCode AS wellNum,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '01',c.useWater,NULL) ), 3 ) AS water01,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '02',c.useWater,NULL) ), 3 ) AS water02,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '03',c.useWater,NULL) ), 3 ) AS water03,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '04',c.useWater,NULL) ), 3 ) AS water04,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '05',c.useWater,NULL) ), 3 ) AS water05,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '06',c.useWater,NULL) ), 3 ) AS water06,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '07',c.useWater,NULL) ), 3 ) AS water07,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '08',c.useWater,NULL) ), 3 ) AS water08,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '09',c.useWater,NULL) ), 3 ) AS water09,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '10',c.useWater,NULL) ), 3 ) AS water10,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '11',c.useWater,NULL) ), 3 ) AS water11,
	ROUND( SUM( IF(SUBSTR( c.yearMonth, 6, 2 ) = '12',c.useWater,NULL) ), 3 ) AS water12 
FROM
	calc_water_month c
	LEFT JOIN t_s_territory t ON c.territoryCode = t.territorycode
	LEFT JOIN t_s_role_datafunction td ON td.functionid = t.ID 
WHERE
	td.roleid = '2c919ca7810b53d5018112db65dd0150' 
	AND SUBSTR( c.yearMonth, 1, 4 ) = '2022' 
GROUP BY
	c.wellCode
ORDER BY
	c.wellCode,
	c.yearMonth;