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.uj5u.com/shujuku/468090.html

標籤：其他

上一篇：MySQL資料庫備份與還原

下一篇：關于Mysql索引的資料結構