mysql 列转行

-- 示例数据表：sales
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product VARCHAR(50),
    q1 INT,
    q2 INT,
    q3 INT,
    q4 INT
);

-- 插入示例数据
INSERT INTO sales (product, q1, q2, q3, q4) VALUES
('Product A', 100, 150, 200, 250),
('Product B', 80, 120, 160, 200);

-- 使用 UNION ALL 将列转行
SELECT product, 'Q1' AS quarter, q1 AS sales_amount FROM sales
UNION ALL
SELECT product, 'Q2' AS quarter, q2 AS sales_amount FROM sales
UNION ALL
SELECT product, 'Q3' AS quarter, q3 AS sales_amount FROM sales
UNION ALL
SELECT product, 'Q4' AS quarter, q4 AS sales_amount FROM sales;

-- 解释说明：
-- 上述代码将 sales 表中的季度销售数据从列转换为行。
-- 每个季度的数据（q1, q2, q3, q4）分别通过 UNION ALL 操作组合成一行，
-- 并添加了一个新的列 quarter 来标识每个季度。
-- 最终结果是一个包含产品名称、季度和销售金额的表格。