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;
1
2
3
4
5

‘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' 

FROM

  Products1 

GROUP BY product_id ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15