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;

以上就完成了连续时间分组

后续说明：

原是想创建为视图的，但mysql 不支持有参数  只能通过函数或存储过程或新建表来处理；

以上为本人原创：

来源：https://www.cnblogs.com/yan8shi/p/16205620.html
本站部分图文来源于网络，如有侵权请联系删除。