1795. 每个产品在不同商店的价格
【行转列和列转行】
# Write your MySQL query statement below
#列转行
#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 ;
#行转列
#这里'store1'加引号是引用store1的名字
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;
列转行注意事项【group by】
group by 后要用聚合函数将结果输出,上方使用的是sum,使用min.max.avg亦可
行转列注意事项【union、union all】
注意’store1’的意思是将别名store的值全部变为【字符串形式的store1】
后面的price是将store1的值赋给price