某天夜里,公司IT老大突然让我帮忙写个SQL,查询社保断缴次数,我一想,这是在领导面前展示能力的好机会啊,于是兴致勃勃的开始准备。
1.分析问题:社保断缴次数,这个sql的核心不就是查询不连续数据吗。
怎么查询不连续的数据呢,不存在下一条符合条件的数据即可。
2.建demo,模拟测试:
CREATE TABLE `jiaofeibiao` (
`name` varchar(10) DEFAULT NULL,
`nianyue` char(6) DEFAULT NULL,
`jishu` int(3) unsigned DEFAULT NULL,
`para_label` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
nianyue字段为char,不能直接加减,另建一个jishu,作为辅助字段,先测试。
使用‘AAA’进行测试,断缴次数为4,分别是201801,201901,202004,202006
SELECT
a. NAME,
COUNT(*)
FROM
jiaofeibiao a
WHERE
a. NAME = 'AAA'
AND NOT EXISTS (
SELECT
1
FROM
jiaofeibiao b
WHERE
a.jishu + 1 = b.jishu
AND b. NAME = 'AAA'
);
使用GROUP BY分组(这里需要考虑当前日期,如果当前日期是202006,那么AAA的最后一次就不算断缴,次数应该为3,这个问题放到最后解决。)
SELECT
a. NAME,
COUNT(*)
FROM
jiaofeibiao a
WHERE
NOT EXISTS (
SELECT
1
FROM
jiaofeibiao b
WHERE
a.jishu + 1 = b.jishu
AND a. NAME = b. NAME
)
GROUP BY
a. NAME;
通过以上测试,思路基本确定:
1.使用NOT EXIST (select 1 where a.jishu + 1 = b.jishu AND a. NAME = b. NAME)来确定不存在下一条连续记录
2.使用 COUNT(*) 和 GROUP BY 来查询多个用户的断缴次数
3.如果某个用户最后一次缴纳时间为当前月,则最后一次缴纳时间不算断缴
综上,写出最终版SQL
AND a.nianyue != ‘202006’ 可以换成当前月份
SELECT
a.`name`,
COUNT(*)
FROM
jiaofeibiao a
WHERE
NOT EXISTS (
SELECT
1
FROM
jiaofeibiao b
WHERE
DATE_ADD(STR_TO_DATE(CONCAT(a.nianyue, '01'),'%Y%m%d'),INTERVAL 1 MONTH)
= DATE_FORMAT(CONCAT(b.nianyue, '01'),'%Y%m%d')
AND a.`name` = b. NAME
)
AND a.nianyue != '202006'
GROUP BY
a. NAME;