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的情况都可以通过该方法优化，具体问题需要具体对待。