mysql重构

力扣题目链接

列转行

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;

‘store1’ AS store, store1 AS price
给列起别名的示例。在这个例子中，‘store1’ AS store表示将名为’store1’的列的别名设为store，store1 AS price表示将名为store1的列的别名设为price。在查询结果中，store1列将以store的名称显示，store1列将以price的名称显示。

行转列

SELECT product_id,SUM(IF(store = 'store1', price, NULL)) 'store1',SUM(IF(store = 'store2', price, NULL)) 'store2',SUM(IF(store = 'store3', price, NULL)) 'store3' FROMProducts1 GROUP BY product_id ;