sql语句中多个union all的case when优化

今天有点空，打算把报表项目中前人留下的一段sql优化下。
原sql大致如下(删除了设计公司数据的敏感信息)：

select '现场司机' 环节,'劳斯莱斯' 品牌, 'CIQ 直接喂料' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	and 操作类型='PDI喂料移车' AND 原库位='入库点' AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'			
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, 'CIQ 入暂存' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七') 
	and 操作类型='PDI暂存区移车' AND 原库位='入库点' AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'		
union all
select '现场司机' 环节,'莱斯莱斯' 品牌, '暂存喂料' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七') 
	AND 操作类型='PDI喂料移车' AND 原库位='PDI暂存区' AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'			
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '入库' 采集项目, count(1) 统计数量 from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	AND ((操作类型='移入立体库' AND 原库位 IN('PDI交接区', '维修交接区', 'PDI维修区', 'PDI喂料区', 'PDI暂存区', '待发区', '入库点'))
		or (操作类型='移车' and left(原库位,3) = 'PDI'))
	AND left(当前库位,1) IN('H','W','K','T','A','E','Z','增','临') AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'			
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '发车' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	AND 操作类型='移入待发区' AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'			
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '维修喂料' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七') 
	AND ((操作类型 ='PDI喂料移车' AND 原库位 <> '入库点' AND LEFT(原库位,3)  <> 'PDI') or 操作类型 ='PDI维修移车')
	AND 操作日期 >= '2017-11-01' AND 操作日期 <= '2017-11-30'			
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '维护移车' 采集项目, count(1) 统计数量 from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	AND 操作类型='洗车/保养移车'  AND left(原库位,1) NOT IN('洗','增') AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'		
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '维护返库移车' 采集项目, count(1) 统计数量  from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	AND 操作类型 in('移入立体库','移车') AND 原库位='增值服务区' AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'		
union all
select '现场司机' 环节,'劳斯莱斯' 品牌, '移车' 采集项目, count(1) 统计数量 from test_table
	where 操作人 IN('张三','李四','王五','赵六','钱七')
	and ((操作类型='移车' and left(原库位,3) != 'PDI'  and 原库位 != '增值服务区')
		or (操作类型='PDI暂存区移车' and 原库位 != '入库点')
		or (操作类型='移入立体库' and 原库位 = '临时库位'))  
	AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'

 

原始查询相当于将表格查询了很多遍，然后查询结果进行拼接，这样效率注定不会很高，更主要时代码看着太杂乱，尤其是人员名字比较多的时候，很不利于后期维护。将上述查询语句用case when进行优化，优化后sql如下：

 

 

select '现场司机' 环节,'劳斯莱斯' 品牌,A.subjects 采集项目,count(1) 统计数量 from (
	select (case when 操作类型='PDI喂料移车' AND 原库位='入库点' then 'CIQ 直接喂料'
		when 操作类型='PDI暂存区移车' AND 原库位='入库点' then 'CIQ 入暂存'
		when 操作类型='PDI喂料移车' AND 原库位='PDI暂存区' then '暂存喂料'
		when ((操作类型='移入立体库' AND 原库位 IN('PDI交接区', '维修交接区', 'PDI维修区', 'PDI喂料区', 'PDI暂存区', '待发区', '入库点'))
			or (操作类型='移车' and left(原库位,3) = 'PDI'))
			AND left(当前库位,1) IN('H','W','K','T','A','E','Z','增','临') then '入库'
		when 操作类型='移入待发区' then '发车'
		when (操作类型 ='PDI喂料移车' AND 原库位 <> '入库点' AND LEFT(原库位,3)  <> 'PDI') or 操作类型 ='PDI维修移车' then '维修喂料'
		when 操作类型='洗车/保养移车'  AND left(原库位,1) NOT IN('洗','增') then '维护移车'
		when 操作类型 in('移入立体库','移车') AND 原库位='增值服务区' then '维护返库移车'
		when (操作类型='移车' and left(原库位,3) != 'PDI'  and 原库位 != '增值服务区')
			or (操作类型='PDI暂存区移车' and 原库位 != '入库点')
			or (操作类型='移入立体库' and 原库位 = '临时库位') then '移车'
		else '未确认' end) as subjects
	from TMP_INVENTORY where 操作人 IN('张三','李四','王五','赵六','钱七') AND 操作日期 >= '2017-09-01' AND 操作日期 <= '2017-11-30'
) A group by A.subjects

 

优化后采集项目会多一个“未确认”，这样不仅不会影响原有的结果，反而会在结果中提示开发者有未考虑到的操作情况存在。

 

人员姓名需要外部传入的话，可以参考bg_yf_license_weight ow on od.id = ow.order_id and ow.is_delete = 0

注意：并非所有的用到多个union all的情况都可以通过该方法优化，具体问题需要具体对待。