Mysql 连续时间分组

Tehran

2022 年 06 月 03 日

170 次浏览

暂无评论

3152字数

Mysql

该方案：不限于本例的时间连续，也可适用于其他按连续分组。

连续条件分组这问题困扰了很久，之前觉得在SQL上很难处理，都是在程序上做处理。后面实在有太多这需求了，所以只能想办法在SQL上处理了。

如下是处理的过程：

思路是使用变量逐行将上行和当前行进行对比条件满足则生成分组的编号，再根据分组条件和分组编号分组就可以；

原数据：

SELECT P_PmsCode,P_CreatorUserName,P_CreatorTime,P_ClockinDate
FROM `pm_attendancerecord`
WHERE P_PmsCode!='0'
ORDER  BY P_PmsCode,P_CreatorUserName,P_CreatorTime

一：先按要分组的条件和时间排序查询并增加上一行的数据好后面进行对比，得到连续的数据；　

SELECT P_PmsCode,P_CreatorUserName,P_CreatorTime,P_ClockinDate
,@RPMS := @RPMS as pms1, @RPMS := P_PmsCode as pms2
,@RNAME := @RNAME as mane1, @RNAME := P_CreatorUserName as mane2
,@Rdate := @Rdate as date1, @Rdate := P_ClockinDate as date2
FROM `pm_attendancerecord` ,(SELECT @RPMS := 0) as a,(SELECT @RNAME := 0) as b,(SELECT @Rdate := '2001-01-01') as c
WHERE P_PmsCode!='0'
ORDER  BY P_PmsCode,P_CreatorUserName,P_CreatorTime

二：条件对比生成分组编码

select t1.*,
IF((pms1=pms2 and mane1=mane2 and (date1=date2 or DATE_ADD(date1,INTERVAL 1 DAY)= date2)),(@Gid := @Gid),(@Gid:=UUID())) as groupid
from (
        SELECT P_PmsCode,P_CreatorUserName,P_CreatorTime,P_ClockinDate
        ,@RPMS := @RPMS as pms1, @RPMS := P_PmsCode as pms2
        ,@RNAME := @RNAME as mane1, @RNAME := P_CreatorUserName as mane2
        ,@Rdate := @Rdate as date1, @Rdate := P_ClockinDate as date2
        FROM `pm_attendancerecord` ,(SELECT @RPMS := 0) as a,(SELECT @RNAME := 0) as b,(SELECT @Rdate := '2001-01-01') as c
        WHERE P_PmsCode!='0'
        ORDER  BY P_PmsCode,P_CreatorUserName,P_CreatorTime
 
) as t1, (SELECT @Gid := UUID()) as t2

三：根据上面的结果再进行分组就可能实现

select P_PmsCode,P_CreatorUserName,MIN(P_ClockinDate) as P_MinDate, MAX(P_ClockinDate) as P_MaxDate
    from (
                    select t1.*,
                    IF((pms1=pms2 and mane1=mane2 and (date1=date2 or DATE_ADD(date1,INTERVAL 1 DAY)= date2)),
                    (@Gid := @Gid),(@Gid:=UUID())
                    ) as groupid
                    from (
                            SELECT P_PmsCode,P_CreatorUserName,P_CreatorTime,P_ClockinDate
                            ,@RPMS := @RPMS as pms1, @RPMS := P_PmsCode as pms2
                            ,@RNAME := @RNAME as mane1, @RNAME := P_CreatorUserName as mane2
                            ,@Rdate := @Rdate as date1, @Rdate := P_ClockinDate as date2
                            FROM `pm_attendancerecord` ,(SELECT @RPMS := 0) as a,(SELECT @RNAME := 0) as b,(SELECT @Rdate := '2001-01-01') as c
                            WHERE P_PmsCode!='0'
                            ORDER  BY P_PmsCode,P_CreatorUserName,P_CreatorTime
 
                    ) as t1, (SELECT @Gid := UUID()) as t2
    ) t3 GROUP BY P_PmsCode,P_CreatorUserName,groupid;