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

最新推荐文章于 2024-05-06 23:01:53 发布

码农王山

最新推荐文章于 2024-05-06 23:01:53 发布

阅读量1.1k

点赞数

分类专栏： mysql 文章标签： mysql case when then

本文链接：https://blog.csdn.net/wangshan_1121/article/details/73350566

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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

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)