要达到的效果：
MySQL不支持动态行转列
原始数据：
以行的方式存储
CREATE TABLE product_sales (id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(50) NOT NULL,category VARCHAR(50) NOT NULL,sales_volume INT NOT NULL,sales_date DATE NOT NULL
);INSERT INTO product_sales (product_name, category, sales_volume, sales_date) VALUES
('iPhone', '电子产品', 50, '2025-01-01'),
('MacBook', '电子产品', 30, '2025-01-01'),
('iPad', '电子产品', 40, '2025-01-01'),
('衬衫', '服装', 60, '2025-01-01'),
('牛仔裤', '服装', 55, '2025-01-01'),
('连衣裙', '服装', 70, '2025-01-01'),
('苹果', '食品', 80, '2025-01-01'),
('牛奶', '食品', 75, '2025-01-01'),
('面包', '食品', 65, '2025-01-01'),
('iPhone', '电子产品', 45, '2025-01-02'),
('MacBook', '电子产品', 25, '2025-01-02'),
('iPad', '电子产品', 35, '2025-01-02'),
('衬衫', '服装', 55, '2025-01-02'),
('牛仔裤', '服装', 50, '2025-01-02'),
('连衣裙', '服装', 65, '2025-01-02'),
('苹果', '食品', 95, '2025-01-05'),
('牛奶', '食品', 90, '2025-01-05'),
('面包', '食品', 80, '2025-01-05');
行转列：
列的形成，使用关键字CASE WHEN
SELECTsales_date AS '销售日期',CASE WHEN product_name = 'iPhone' THEN sales_volume ELSE 0 END AS 'iPhone',CASE WHEN product_name = 'MacBook' THEN sales_volume ELSE 0 END AS 'MacBook',CASE WHEN product_name = 'iPad' THEN sales_volume ELSE 0 END AS 'iPad',CASE WHEN product_name = '衬衫' THEN sales_volume ELSE 0 END AS '衬衫',CASE WHEN product_name = '牛仔裤' THEN sales_volume ELSE 0 END AS '牛仔裤',CASE WHEN product_name = '连衣裙' THEN sales_volume ELSE 0 END AS '连衣裙',CASE WHEN product_name = '苹果' THEN sales_volume ELSE 0 END AS '苹果',CASE WHEN product_name = '牛奶' THEN sales_volume ELSE 0 END AS '牛奶',CASE WHEN product_name = '面包' THEN sales_volume ELSE 0 END AS '面包',sales_volume AS '日总销量'
FROMproduct_sales;
结果：
形成了这样的大表
去除冗余数据：
SELECTsales_date AS '销售日期',SUM(CASE WHEN product_name = 'iPhone' THEN sales_volume ELSE 0 END) AS 'iPhone',SUM(CASE WHEN product_name = 'MacBook' THEN sales_volume ELSE 0 END) AS 'MacBook',SUM(CASE WHEN product_name = 'iPad' THEN sales_volume ELSE 0 END) AS 'iPad',SUM(CASE WHEN product_name = '衬衫' THEN sales_volume ELSE 0 END) AS '衬衫',SUM(CASE WHEN product_name = '牛仔裤' THEN sales_volume ELSE 0 END) AS '牛仔裤',SUM(CASE WHEN product_name = '连衣裙' THEN sales_volume ELSE 0 END) AS '连衣裙',SUM(CASE WHEN product_name = '苹果' THEN sales_volume ELSE 0 END) AS '苹果',SUM(CASE WHEN product_name = '牛奶' THEN sales_volume ELSE 0 END) AS '牛奶',SUM(CASE WHEN product_name = '面包' THEN sales_volume ELSE 0 END) AS '面包',SUM(sales_volume) AS '日总销量'
FROMproduct_sales
GROUP BYsales_date
ORDER BYsales_date;
结果：
列转行：
数据：
CREATE TABLE sales_data (sale_date DATE PRIMARY KEY,p_001 INT,p_002 INT,p_003 INT,p_004 INT,p_005 INT,p_006 INT,p_007 INT,p_008 INT,p_009 INT,p_010 INT
);
INSERT INTO sales_data (sale_date, p_001, p_002, p_003, p_004, p_005, p_006, p_007, p_008, p_009, p_010) VALUES
('2025-01-01', 50, 30, 40, 60, 55, 70, 80, 75, 65, 525),
('2025-01-02', 45, 25, 35, 55, 50, 65, 75, 70, 60, 480),
('2025-01-03', 55, 35, 45, 65, 60, 75, 85, 80, 70, 570),
('2025-01-04', 60, 40, 50, 70, 65, 80, 90, 85, 75, 615),
('2025-01-05', 65, 45, 55, 75, 70, 85, 95, 90, 80, 660);
操作：
使用UNION ALL关键字
SELECTsale_date,'p_001' AS product_id,p_001 AS sales_amount
FROM sales_data
UNION ALL
SELECTsale_date,'p_002' AS product_id,p_002 AS sales_amount
FROM sales_data
UNION ALL
SELECTsale_date,'p_003' AS product_id,p_003 AS sales_amount
FROM sales_data.......
结果：
)
