MySQL 行转列

一、从图一转为图二

• 图一：
  

• 图二：
  

---

二、SQL:

• 图一sql：

SELECT warehouse_code, operate_type, sum(qty) 
FROM `warehouse_operate_record`
group by warehouse_code, operate_type
;

• 图二sql：

SELECT a.warehouse_code, 
sum(if(a.operate_type = 'container', a.qty, 0)) AS container,
sum(if(a.operate_type = 'tally', a.qty, 0)) AS tally,
sum(if(a.operate_type = 'putaway', a.qty, 0)) AS putaway,
sum(if(a.operate_type = 'pickup', a.qty, 0)) AS pickup,
sum(if(a.operate_type = 'label', a.qty, 0)) AS label,
sum(if(a.operate_type = 'outbound', a.qty, 0)) AS outbound,
sum(if(a.operate_type = 'cycle_count', a.qty, 0)) AS cycle_count,
sum(if(a.operate_type = 'transfer', a.qty, 0)) AS transfer
FROM (
	SELECT warehouse_code, operate_type, sum(qty) as qty
	FROM `warehouse_operate_record`
	-- where warehouse_code = ''
	group by warehouse_code, operate_type
) a
group by a.warehouse_code
;

---

欢迎关注公众号：慌途L
后面会慢慢将文章迁移至公众号，也是方便在没有电脑的情况下可以进行翻阅，更新的话会两边同时更新，大家不用担心！

---