MySQL 行列变换

MySQL 行列变换

product_id	store1	store2	store3
0	95	100	105
1	70	null	80

product_id	store	price
0	store1	95
0	store2	100
0	store3	105
1	store1	70
1	store3	80

注意：product_id为产品的id，store1，store2, store3,为三个门店，price为商品价格

列变行（group by + sum if）

select product_id,
		sum(if(store='store1',price,null)) as store1,
		sum(if(store='store2',price,null)) as store2,
		sum(if(store='store3',price,null)) as store3
        from Product
group by product_id
		

行变列(union all)

select product_id,'store1' as store,store1 price from Product
where store1 is not null
union all
select product_id,'store2' as store,store2 price from Product
where store2 is not null
union all
select product_id,'store3' as store,store3 price from Product
where store3 is not null