## 解決連續範圍問題，運用到一個小小技巧


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. 1~3
2. 100~101
3. 103~105

## 第一步

1. 將資料按照順序排巡(RK)


+-----+------+
| num | RK   |
+-----+------+
|   1 |    1 |
|   2 |    2 |
|   3 |    3 |
| 100 |    4 |
| 101 |    5 |
| 103 |    6 |
| 104 |    7 |
| 105 |    8 |
+-----+------+

### 有了上表我們就可以變魔術摟~~請看



+-----+------+-----------+
| 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

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