该方案不限于本例的连续时间，还可以应用于其他连续组。
[En]
The scheme is not limited to the continuous time of this example, but can also be applied to other consecutive groups.
连续条件 分组这问题困扰了很久，之前觉得在SQL上很难处理，都是在程序上做处理。后面实在有太多这需求了，所以只能想办法在SQL上处理了。
如下是处理的过程：
思路是使用变量 逐行将上行和当前行进行对比 条件满足则生成分组的编号，再根据分组条件和分组编号分组就可以；
原数据：
;gutter:true;
SELECT P_PmsCode,P_CreatorUserName,P_CreatorTime,P_ClockinDate
FROM
WHERE P_PmsCode!='0'
ORDER BY P_PmsCode,P_CreatorUserName,P_CreatorTime</p>
<pre><code>
首先按条件和时间对查询进行排序，将前一行的数据相加，比对后得到连续的数据。<details><summary>*<font color='gray'>[En]</font>*</summary>*<font color='gray'>First, sort the query according to the conditions and time to be grouped and add the data of the previous row to get continuous data after comparison.</font>*</details>
;gutter:true;
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
第二，条件比较生成块编码。
[En]
Second, condition comparison generates block coding.
;gutter:true;
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 ,(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</p>
<pre><code> ) as t1, (SELECT @Gid := UUID()) as t2
</code></pre>
<pre><code>
三：根据上面的结果 再进行分组就可能实现
;gutter:true;
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;
连续时间分组在上面完成。
[En]
The continuous time grouping is completed above.
后续说明：
原是想创建为视图的，但mysql 不支持有参数 只能通过函数或存储过程或新建表来处理；
以上为本人原创：
Original: https://www.cnblogs.com/yan8shi/p/16205620.html
Author: 黄衍仕
Title: Mysql 连续时间分组
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505442/
转载文章受原作者版权保护。转载请注明原作者出处！