有個需求要做找出連續出勤超過五天的人
下圖是Log表
期望結果找出 如下表連續出勤超過五天的人
這是一個很經典的 [SQL連續範圍] 問題
解決連續範圍問題，運用到一個小小技巧
連續資料有個特性是一組連續範圍數值 - 有順序的值 結果是一樣的
如下圖 我們可發現有三組連續數字
DECLARE @t TABLE( a int );
INSERT into @t VALUES(1);
INSERT into @t VALUES(2);
INSERT into @t VALUES(3);
INSERT into @t VALUES(100);
INSERT into @t VALUES(101);
INSERT into @t VALUES(103);
INSERT into @t VALUES(104);
INSERT into @t VALUES(105);
+-----+
| a |
+-----+
| 1 |
| 2 |
| 3 |
| 100 |
| 101 |
| 103 |
| 104 |
| 105 |
+-----+
分別是
- 1~3
- 100~101
- 103~105
第一步
多新增一組序號(RK)
- 將資料按照順序排巡(RK)
就可得到如下表
+-----+------+
| num | RK |
+-----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 100 | 4 |
| 101 | 5 |
| 103 | 6 |
| 104 | 7 |
| 105 | 8 |
+-----+------+
有了上表我們就可以變魔術摟~~請看
利用(num - RK) 可以得到分組的資料(真是太神奇啦XD)
+-----+------+-----------+
| num | RK | num - RK |
+-----+------+-----------+
| 1 | 1 | 0 |
| 2 | 2 | 0 |
| 3 | 3 | 0 |
| 100 | 4 | 96 |
| 101 | 5 | 96 |
| 103 | 6 | 97 |
| 104 | 7 | 97 |
| 105 | 8 | 97 |
+-----+------+-----------+
有了上面概念後就可運用此腳本來取得分組資訊
SELECT MIN(T.num) fromNum,MAX( T.num) TONum
FROM
(
SELECT *,num - ROW_NUMBER() OVER(ORDER BY num) RKG
FROM @t
) AS T
GROUP BY RKG
Result：
fromNum TONum
----------- -----------
1 3
100 101
103 105
回歸正題
題目中的連續資料是日期，所以我們無法像數字一樣直接相減排序。
假如可以按照日期來排出序號該有多好．．．
好在SQL_Server有提供一個關鍵字是 OVER 視窗函式
我們就可運用 ROW_NUMBER() ＋ OVER 來排序
話不多說貼上程式碼XD
DECLARE @T TABLE
(
Name VARCHAR(50),
Dates DATE
);
INSERT INTO @T VALUES
('Tom','2017/10/10'),
('Tom','2017/10/11'),
('Tom','2017/10/12'),
('Tom','2017/10/13'),
('Tom','2017/10/14'),
('AMY','2017/10/10'),
('AMY','2017/10/11'),
('AMY','2017/10/12');
SELECT Name,MIN(DATES) AS [StartDate],MAX(DATES) AS [EndDate]
FROM
(
SELECT *, ROW_NUMBER() OVER(ORDER BY [Name]) - ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY DATES) as rn
FROM @T
)AS T
GROUP BY NAME
HAVING COUNT(RN) > 4
小結 ：
查找連續範圍問題重點在如何把資料分群
只要分群後就只要Group by 搂^^
如果本文對您幫助很大，可街口支付斗內鼓勵石頭^^
