MySQL 行转列_mysql 行转列csdn

一、从图一转为图二

• 图一：
  

• 图二：
  

二、SQL:

• 图一sql：

SELECT warehouse_code, operate_type, sum(qty) 
FROM `warehouse_operate_record`
group by warehouse_code, operate_type
;
1
2
3
4

• 图二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
;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17