行列转换：MySQL中的数据变形魔法

行转列

使用CASE函数+聚合函数

SELECT
    MAX(CASE WHEN salesperson = 'John' THEN sales_amount END) AS John_Sales,
    MAX(CASE WHEN salesperson = 'Alice' THEN sales_amount END) AS Alice_Sales
FROM sales_data;

1
2
3
4
5

列转行

使用UNIO连接每列数据

SELECT product_id,'store1' store,store1 price FROM products WHERE store1 IS NOT NULL
UNION 
SELECT product_id,'store2' store,store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id,'store3' store,store3 price FROM products WHERE store3 IS NOT NULL
1
2
3
4
5