首先,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;
explain索引分析如下:
效率还是可以的