查询结果中某列包含固定数据

查询结果中某列包含固定数据

数据:

• 欠薪预警表(salary_alarm)所用字段(id , addr , date);
• 纠纷统计表(tbl_disput)所用字段(dispute_code , addr , now_operation);

需求:

• 查询出2019年后20个镇街各欠薪预警总数和已处理数, 查询结果显示20个镇街没有的显示为0;

思路:

1. 2019年后可用date_format()函数判断,
2. 镇街的判断来源于addr字段, 由于addr中与镇街名字是包含关系, 可以用like模糊查询,
3. 已处理的判断来源于now_operation字段,
4. 各镇街数据, 使用group by 做分组查询,
5. 显示固定的20个镇街数据, 可使用left join on 做表连接查询.

sql语句:

1. DATE_FORMAT(date,'%Y')>=2019
   

2. CASE WHEN (sa.addr LIKE '%东湖%' OR sa.addr LIKE '%经济技术开发区%') THEN '东湖街道'
   

3. SUM(CASE WHEN sa.now_operation IN('事件关闭','关闭','处理完成') THEN 1 ELSE 0 END) processedCount
   

4. GROUP BY addr
   

   显示满足条件的sql

   #各镇街预警数量
   SELECT
   	sa.addr addr,COUNT(1) total,SUM(CASE WHEN sa.now_operation IN('事件关闭','关闭','处理完成') THEN 1 ELSE 0 END) processedCount
   FROM
   	(SELECT (CASE WHEN (sa.addr LIKE '%东湖%' OR sa.addr LIKE '%经济技术开发区%') THEN '东湖街道'
   	WHEN sa.addr LIKE '%中泰%' THEN '中泰街道'
   	WHEN sa.addr LIKE '%临平%' THEN '临平街道'
   	WHEN sa.addr LIKE '%乔司%' THEN '乔司街道'
   	WHEN sa.addr LIKE '%五常%' THEN '五常街道'
   	WHEN sa.addr LIKE '%仁和%' THEN '仁和街道'
   	WHEN sa.addr LIKE '%仓前%' THEN '仓前街道'
   	WHEN sa.addr LIKE '%余杭街道%' THEN '余杭街道'
   	WHEN sa.addr LIKE '%南苑%' THEN '南苑街道'
   	WHEN sa.addr LIKE '%塘栖%' THEN '塘栖镇'
   	WHEN sa.addr LIKE '%崇贤%' THEN '崇贤街道'
   	WHEN sa.addr LIKE '%径山%' THEN '径山镇'
   	WHEN sa.addr LIKE '%星桥%' THEN '星桥街道'
   	WHEN sa.addr LIKE '%瓶窑%' THEN '瓶窑镇'
   	WHEN sa.addr LIKE '%良渚%' THEN '良渚街道'
   	WHEN sa.addr LIKE '%运河%' THEN '运河街道'
   	WHEN sa.addr LIKE '%闲林%' THEN '闲林街道'
   	WHEN sa.addr LIKE '%鸬鸟%' THEN '鸬鸟镇'
   	WHEN sa.addr LIKE '%百丈%' THEN '百丈镇'
   	WHEN sa.addr LIKE '%黄湖%' THEN '黄湖镇' 
   	END) addr,date,now_operation FROM salary_alarm sa LEFT JOIN tbl_dispute dpt ON sa.id = dpt.dispute_code) sa
   WHERE
   	DATE_FORMAT(date,'%Y')>=2018
   GROUP BY addr;
   

   查询结果
   ![Snipaste_2019-04-04_09-02-43](.\assets\Snipaste_2019-04-04_09-02-43.png

   2019年以后查询结果:

   ![Snipaste_2019-04-04_09-04-42](.\assets\Snipaste_2019-04-04_09-04-42.png

5. 查询结果中并未包含全部20个镇街数据, 若要包含某张表的全部数据这里考虑到可以使用左外连接来查询, 将20 个镇街数据作为左表 , 最后再采用group by ,最终sql为:

   #各镇街预警数量
   SELECT 
   t.addr,SUM(CASE WHEN t.addr = sa.addr THEN 1 ELSE 0 END) total,SUM(CASE WHEN sa.now_operation IN('事件关闭','关闭','处理完成') THEN 1 ELSE 0 END) processedCount
   FROM
   (SELECT '东湖街道' addr FROM DUAL UNION 
   SELECT '中泰街道' addr FROM DUAL UNION 
   SELECT '临平街道' addr FROM DUAL UNION 
   SELECT '乔司街道' addr FROM DUAL UNION 
   SELECT '五常街道' addr FROM DUAL UNION 
   SELECT '仁和街道' addr FROM DUAL UNION 
   SELECT '仓前街道' addr FROM DUAL UNION 
   SELECT '余杭街道' addr FROM DUAL UNION 
   SELECT '南苑街道' addr FROM DUAL UNION 
   SELECT '塘栖镇' addr FROM DUAL UNION 
   SELECT '崇贤街道' addr FROM DUAL UNION 
   SELECT '径山镇' adrr FROM DUAL UNION 
   SELECT '星桥街道' adrr FROM DUAL UNION 
   SELECT '瓶窑镇' adrr FROM DUAL UNION 
   SELECT '良渚街道' adrr FROM DUAL UNION 
   SELECT '运河街道' adrr FROM DUAL UNION
   SELECT '闲林街道' adrr FROM DUAL UNION
   SELECT '鸬鸟镇' adrr FROM DUAL UNION
   SELECT '百丈镇' adrr FROM DUAL UNION
   SELECT '黄湖镇'  adrr FROM DUAL) t LEFT JOIN
   (SELECT (CASE WHEN (sa.addr LIKE '%东湖%' OR sa.addr LIKE '%经济技术开发区%') THEN '东湖街道'
   	WHEN sa.addr LIKE '%中泰%' THEN '中泰街道'
   	WHEN sa.addr LIKE '%临平%' THEN '临平街道'
   	WHEN sa.addr LIKE '%乔司%' THEN '乔司街道'
   	WHEN sa.addr LIKE '%五常%' THEN '五常街道'
   	WHEN sa.addr LIKE '%仁和%' THEN '仁和街道'
   	WHEN sa.addr LIKE '%仓前%' THEN '仓前街道'
   	WHEN sa.addr LIKE '%余杭街道%' THEN '余杭街道'
   	WHEN sa.addr LIKE '%南苑%' THEN '南苑街道'
   	WHEN sa.addr LIKE '%塘栖%' THEN '塘栖镇'
   	WHEN sa.addr LIKE '%崇贤%' THEN '崇贤街道'
   	WHEN sa.addr LIKE '%径山%' THEN '径山镇'
   	WHEN sa.addr LIKE '%星桥%' THEN '星桥街道'
   	WHEN sa.addr LIKE '%瓶窑%' THEN '瓶窑镇'
   	WHEN sa.addr LIKE '%良渚%' THEN '良渚街道'
   	WHEN sa.addr LIKE '%运河%' THEN '运河街道'
   	WHEN sa.addr LIKE '%闲林%' THEN '闲林街道'
   	WHEN sa.addr LIKE '%鸬鸟%' THEN '鸬鸟镇'
   	WHEN sa.addr LIKE '%百丈%' THEN '百丈镇'
   	WHEN sa.addr LIKE '%黄湖%' THEN '黄湖镇' 
   	END) addr,date,now_operation FROM salary_alarm sa LEFT JOIN tbl_dispute dpt ON sa.id = dpt.dispute_code WHERE DATE_FORMAT(date,'%Y')>=2019) sa
   ON t.addr = sa.addr
   GROUP BY addr;
   

   查询结果:

![Snipaste_2019-04-04_09-11-45](.\assets\Snipaste_2019-04-04_09-11-45.png

注: 此sql查询中含有模糊查询当数据量庞大时效率低,后期可考虑使用全文检索技术.