mysql我所遇到的问题case when then的使用

在项目中出的问题总结方便以后自己查看也希望能帮到遇到类似问题的朋友，最近项目中有个需求就是省级用户查看申请时候，如果是县级用户申请的要把县级用户合并的市级用户中，如果是市级用户只需要统计就行。已知市级用户开头的四位数字。

select 


CASE 
WHEN substring(ptgw.xzqh,1,4) = '3301' THEN '杭州'
WHEN substring(ptgw.xzqh,1,4) = '3303' THEN '温州'
WHEN substring(ptgw.xzqh,1,4) = '3304' THEN '嘉兴'
WHEN substring(ptgw.xzqh,1,4) = '3305' THEN '湖州'
WHEN substring(ptgw.xzqh,1,4) = '3306' THEN '绍兴'
WHEN substring(ptgw.xzqh,1,4) = '3307' THEN '金华'
WHEN substring(ptgw.xzqh,1,4) = '3308' THEN '衢州'
WHEN substring(ptgw.xzqh,1,4) = '3309' THEN '舟山'
WHEN substring(ptgw.xzqh,1,4) = '3310' THEN '台州'
WHEN substring(ptgw.xzqh,1,4) = '3311' THEN '丽水' END as sxmc,


CASE 
WHEN substring(ptgw.xzqh,1,4) = '3301' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3301'))
WHEN substring(ptgw.xzqh,1,4) = '3303' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3303'))
WHEN substring(ptgw.xzqh,1,4) = '3304' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3304'))
WHEN substring(ptgw.xzqh,1,4) = '3305' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3305'))
WHEN substring(ptgw.xzqh,1,4) = '3306' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3306'))
WHEN substring(ptgw.xzqh,1,4) = '3307' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3307'))
WHEN substring(ptgw.xzqh,1,4) = '3308' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3308'))
WHEN substring(ptgw.xzqh,1,4) = '3309' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3309'))
WHEN substring(ptgw.xzqh,1,4) = '3310' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3310'))
WHEN substring(ptgw.xzqh,1,4) = '3311' THEN (select count(*) from fm_ws_ptgw_jd where zt in(0,-1,-2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3311')) END as undeclareCounts,


CASE 
WHEN substring(ptgw.xzqh,1,4) = '3301' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3301')) 
WHEN substring(ptgw.xzqh,1,4) = '3303' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3303'))
WHEN substring(ptgw.xzqh,1,4) = '3304' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3304'))
WHEN substring(ptgw.xzqh,1,4) = '3305' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3305'))
WHEN substring(ptgw.xzqh,1,4) = '3306' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3306'))
WHEN substring(ptgw.xzqh,1,4) = '3307' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3307'))
WHEN substring(ptgw.xzqh,1,4) = '3308' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3308'))
WHEN substring(ptgw.xzqh,1,4) = '3309' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3309'))
WHEN substring(ptgw.xzqh,1,4) = '3310' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3310'))
WHEN substring(ptgw.xzqh,1,4) = '3311' THEN (select count(*) from fm_ws_ptgw_jd where zt in(1,2) and nd=2017 and yf=6 and substring(fm_ws_ptgw_jd.xzqh,1,4) LIKE CONCAT ('3311')) END as alreadydeclareCounts
from fm_ws_ptgw_jd  ptgw
group by substring(ptgw.xzqh,1,4) 

数据库表的数据：

查询结果：