行转列不再复杂：SQL高手都在用的技巧揭秘

CREATE TABLE`sales`  (
`year`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL,
`quarter`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL,
`revenue`varchar(255) CHARACTERSET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULLDEFAULTNULL
) ENGINE = InnoDBCHARACTERSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;


INSERTINTO`sales`VALUES ('2023', 'Q2', '7000');
INSERTINTO`sales`VALUES ('2023', 'Q3', '6000');
INSERTINTO`sales`VALUES ('2023', 'Q4', '8000');
INSERTINTO`sales`VALUES ('2023', 'Q1', '5000');

SELECT
  year,
SUM(CASEWHENquarter = 'Q1'THEN revenue ELSE0END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue ELSE0END) AS Q2,
SUM(CASEWHENquarter = 'Q3'THEN revenue ELSE0END) AS Q3,
SUM(CASEWHENquarter = 'Q4'THEN revenue ELSE0END) AS Q4
FROM sales
GROUPBYyear;

SELECT *
FROM sales
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS pivot_table;

-- 1. 获取所有列名
SET @cols = NULL;
SELECTGROUP_CONCAT(DISTINCTCONCAT('SUM(CASE WHEN quarter = ''', quarter, ''' THEN revenue END) AS ', quarter))
INTO @cols
FROM sales;

-- 2. 拼接完整SQL语句
SET @sql = CONCAT(
'SELECT year, ', @cols,
' FROM sales
   GROUP BY year'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

SELECT
  year,
  product,
SUM(CASEWHENquarter = 'Q1'THEN revenue ELSE0END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue ELSE0END) AS Q2
FROM sales
GROUPBYyear, product;

SELECT region, [线上], [线下]
FROM (
  SELECT region, channel, count
  FROM user_registration
) AS src
PIVOT (
  SUM(count)
  FOR channel IN ([线上], [线下])
) AS pvt;

-- 替换NULL为0
COALESCE(SUM(...), 0) AS column_name

SELECT
  year,
SUM(CASEWHENquarter = 'Q1'THEN revenue END) AS Q1,
SUM(CASEWHENquarter = 'Q2'THEN revenue END) AS Q2,
SUM(CASEWHENquarter = 'Q3'THEN revenue END) AS Q3,
SUM(CASEWHENquarter = 'Q4'THEN revenue END) AS Q4,
-- 计算累计值
SUM(revenue) OVER (PARTITIONBYyearORDERBYquarter) AS cumulative
FROM sales
GROUPBYyear, quarter;