mysql 查询行转列

CREATE TABLE sales (
    id INT PRIMARY KEY,
    product VARCHAR(50),
    month VARCHAR(10),
    amount DECIMAL(10, 2)
);

SELECT product,
       MAX(CASE WHEN month = 'January' THEN amount ELSE 0 END) AS January,
       MAX(CASE WHEN month = 'February' THEN amount ELSE 0 END) AS February,
       -- ... 其他月份
FROM sales
GROUP BY product;

CREATE TEMPORARY TABLE months (month VARCHAR(10));
INSERT INTO months VALUES ('January'), ('February'), ('March'), -- ... 其他月份

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN s.month = ''', s.month, ''' THEN s.amount ELSE 0 END) AS ', s.month)) INTO @sql
FROM sales s, months m;

SET @sql = CONCAT('SELECT s.product, ', @sql, ' FROM sales s GROUP BY s.product');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;