---------------------------------------------------------------------਀ⴀⴀ 䜀愀瀀猀 愀渀搀 䤀猀氀愀渀搀猀
-- Itzik Ben-Gan, Solid Quality Mentors (www.SolidQ.com)਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
਀匀䔀吀 一伀䌀伀唀一吀 伀一㬀
USE tempdb;਀
-- dbo.NumSeq (numeric sequence with unique values, interval: 1)਀䤀䘀 伀䈀䨀䔀䌀吀开䤀䐀⠀✀搀戀漀⸀一甀洀匀攀焀✀Ⰰ ✀唀✀⤀ 䤀匀 一伀吀 一唀䰀䰀 䐀刀伀倀 吀䄀䈀䰀䔀 搀戀漀⸀一甀洀匀攀焀㬀
਀䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 搀戀漀⸀一甀洀匀攀焀
(਀  猀攀焀瘀愀氀 䤀一吀 一伀吀 一唀䰀䰀
    CONSTRAINT PK_NumSeq PRIMARY KEY਀⤀㬀
਀䤀一匀䔀刀吀 䤀一吀伀 搀戀漀⸀一甀洀匀攀焀⠀猀攀焀瘀愀氀⤀ 嘀䄀䰀唀䔀匀
  (2),(3),(11),(12),(13),(27),(33),(34),(35),(42);਀
-- dbo.BigNumSeq (big numeric sequence with unique values, interval: 1)਀䤀䘀 伀䈀䨀䔀䌀吀开䤀䐀⠀✀搀戀漀⸀䈀椀最一甀洀匀攀焀✀Ⰰ ✀唀✀⤀ 䤀匀 一伀吀 一唀䰀䰀 䐀刀伀倀 吀䄀䈀䰀䔀 搀戀漀⸀䈀椀最一甀洀匀攀焀㬀
਀䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 搀戀漀⸀䈀椀最一甀洀匀攀焀
(਀  猀攀焀瘀愀氀 䤀一吀 一伀吀 一唀䰀䰀
    CONSTRAINT PK_BigNumSeq PRIMARY KEY਀⤀㬀
਀ⴀⴀ 倀漀瀀甀氀愀琀攀 琀愀戀氀攀 眀椀琀栀 瘀愀氀甀攀猀 椀渀 琀栀攀 爀愀渀最攀 ㄀ 琀栀爀漀甀最栀 琀漀 ㄀　Ⰰ　　　Ⰰ　　　
-- with a gap every 1000 (total 10,000 gaps)਀圀䤀吀䠀
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),਀䰀㄀   䄀匀⠀匀䔀䰀䔀䌀吀 ㄀ 䄀匀 挀 䘀刀伀䴀 䰀　 䄀匀 䄀Ⰰ 䰀　 䄀匀 䈀⤀Ⰰ
L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),਀䰀㌀   䄀匀⠀匀䔀䰀䔀䌀吀 ㄀ 䄀匀 挀 䘀刀伀䴀 䰀㈀ 䄀匀 䄀Ⰰ 䰀㈀ 䄀匀 䈀⤀Ⰰ
L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),਀䰀㔀   䄀匀⠀匀䔀䰀䔀䌀吀 ㄀ 䄀匀 挀 䘀刀伀䴀 䰀㐀 䄀匀 䄀Ⰰ 䰀㐀 䄀匀 䈀⤀Ⰰ
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)਀䤀一匀䔀刀吀 䤀一吀伀 搀戀漀⸀䈀椀最一甀洀匀攀焀 圀䤀吀䠀⠀吀䄀䈀䰀伀䌀䬀⤀ ⠀猀攀焀瘀愀氀⤀
  SELECT n਀  䘀刀伀䴀 一甀洀猀
  WHERE n <= 10000000਀    䄀一䐀 渀 ─ ㄀　　　 㰀㸀 　㬀
਀ⴀⴀ 搀戀漀⸀吀攀洀瀀匀攀焀 ⠀琀攀洀瀀漀爀愀氀 猀攀焀甀攀渀挀攀 眀椀琀栀 甀渀椀焀甀攀 瘀愀氀甀攀猀Ⰰ 椀渀琀攀爀瘀愀氀㨀 㐀 栀漀甀爀猀⤀
IF OBJECT_ID('dbo.TempSeq', 'U') IS NOT NULL DROP TABLE dbo.TempSeq;਀
CREATE TABLE dbo.TempSeq਀⠀
  seqval DATETIME NOT NULL਀    䌀伀一匀吀刀䄀䤀一吀 倀䬀开吀攀洀瀀匀攀焀 倀刀䤀䴀䄀刀夀 䬀䔀夀
);਀
INSERT INTO dbo.TempSeq(seqval) VALUES਀  ⠀✀㈀　　㤀　㈀㄀㈀ 　　㨀　　✀⤀Ⰰ
  ('20090212 04:00'),਀  ⠀✀㈀　　㤀　㈀㄀㈀ ㄀㈀㨀　　✀⤀Ⰰ
  ('20090212 16:00'),਀  ⠀✀㈀　　㤀　㈀㄀㈀ ㈀　㨀　　✀⤀Ⰰ
  ('20090213 08:00'),਀  ⠀✀㈀　　㤀　㈀㄀㌀ ㈀　㨀　　✀⤀Ⰰ
  ('20090214 00:00'),਀  ⠀✀㈀　　㤀　㈀㄀㐀 　㐀㨀　　✀⤀Ⰰ
  ('20090214 12:00');਀
-- dbo.NumSeqDups (numeric sequence with duplicates, interval: 1)਀䤀䘀 伀䈀䨀䔀䌀吀开䤀䐀⠀✀搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀✀Ⰰ ✀唀✀⤀ 䤀匀 一伀吀 一唀䰀䰀 䐀刀伀倀 吀䄀䈀䰀䔀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀㬀
਀䌀刀䔀䄀吀䔀 吀䄀䈀䰀䔀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀
(਀  猀攀焀瘀愀氀 䤀一吀 一伀吀 一唀䰀䰀
);਀䌀刀䔀䄀吀䔀 䌀䰀唀匀吀䔀刀䔀䐀 䤀一䐀䔀堀 椀搀砀开猀攀焀瘀愀氀 伀一 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⠀猀攀焀瘀愀氀⤀㬀
਀䤀一匀䔀刀吀 䤀一吀伀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⠀猀攀焀瘀愀氀⤀ 嘀䄀䰀唀䔀匀
  (2),(2),(2),(3),(11),(12),(12),(13),(27),(27),(27),(27),਀  ⠀㌀㌀⤀Ⰰ⠀㌀㐀⤀Ⰰ⠀㌀㐀⤀Ⰰ⠀㌀㔀⤀Ⰰ⠀㌀㔀⤀Ⰰ⠀㌀㔀⤀Ⰰ⠀㐀㈀⤀Ⰰ⠀㐀㈀⤀㬀
਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
-- Gaps, Solution 1 using subqueries਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
਀ⴀⴀ 一甀洀攀爀椀挀
SELECT seqval਀䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䄀
WHERE NOT EXISTS(SELECT *਀                 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
                 WHERE B.seqval = A.seqval + 1);਀
SELECT਀  猀攀焀瘀愀氀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀
FROM dbo.NumSeq AS A਀圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀⠀匀䔀䰀䔀䌀吀 ⨀
                 FROM dbo.NumSeq AS B਀                 圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⬀ ㄀⤀
  AND seqval < (SELECT MAX(seqval) FROM dbo.NumSeq);਀
SELECT਀  猀攀焀瘀愀氀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ
  (SELECT MIN(B.seqval)਀   䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
   WHERE B.seqval > A.seqval) - 1 AS end_range਀䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䄀
WHERE NOT EXISTS(SELECT *਀                 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
                 WHERE B.seqval = A.seqval + 1)਀  䄀一䐀 猀攀焀瘀愀氀 㰀 ⠀匀䔀䰀䔀䌀吀 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀⤀㬀
਀ⴀⴀ 䈀椀最
-- 8 seconds, 62,262 logical reads਀匀䔀䰀䔀䌀吀
  seqval + 1 AS start_range,਀  ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
   FROM dbo.BigNumSeq AS B਀   圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀 䄀⸀猀攀焀瘀愀氀⤀ ⴀ ㄀ 䄀匀 攀渀搀开爀愀渀最攀
FROM dbo.BigNumSeq AS A਀圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀⠀匀䔀䰀䔀䌀吀 ⨀
                 FROM dbo.BigNumSeq AS B਀                 圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⬀ ㄀⤀
  AND seqval < (SELECT MAX(seqval) FROM dbo.BigNumSeq);਀
-- Temporal਀匀䔀䰀䔀䌀吀
  DATEADD(hour, 4, seqval) AS start_range,਀  䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ ⴀ㐀Ⰰ
    (SELECT MIN(B.seqval)਀     䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䈀
     WHERE B.seqval > A.seqval)) AS end_range਀䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䄀
WHERE NOT EXISTS(SELECT *਀                 䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䈀
                 WHERE B.seqval = DATEADD(hour, 4, A.seqval))਀  䄀一䐀 猀攀焀瘀愀氀 㰀 ⠀匀䔀䰀䔀䌀吀 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀⤀㬀
਀ⴀⴀ 䐀甀瀀猀
SELECT਀  猀攀焀瘀愀氀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ
  (SELECT MIN(B.seqval)਀   䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䈀
   WHERE B.seqval > A.seqval) - 1 AS end_range਀䘀刀伀䴀 ⠀匀䔀䰀䔀䌀吀 䐀䤀匀吀䤀一䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⤀ 䄀匀 䄀
WHERE NOT EXISTS(SELECT *਀                 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䈀
                 WHERE B.seqval = A.seqval + 1)਀  䄀一䐀 猀攀焀瘀愀氀 㰀 ⠀匀䔀䰀䔀䌀吀 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⤀㬀
਀匀䔀䰀䔀䌀吀 䐀䤀匀吀䤀一䌀吀
  seqval + 1 AS start_range,਀  ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
   FROM dbo.NumSeqDups AS B਀   圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀 䄀⸀猀攀焀瘀愀氀⤀ ⴀ ㄀ 䄀匀 攀渀搀开爀愀渀最攀
FROM dbo.NumSeqDups AS A਀圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀⠀匀䔀䰀䔀䌀吀 ⨀
                 FROM dbo.NumSeqDups AS B਀                 圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⬀ ㄀⤀
  AND seqval < (SELECT MAX(seqval) FROM dbo.NumSeqDups);਀
----------------------------------------------------------------------਀ⴀⴀ 䜀愀瀀猀Ⰰ 匀漀氀甀琀椀漀渀 ㈀ 甀猀椀渀最 猀甀戀焀甀攀爀椀攀猀
----------------------------------------------------------------------਀
-- Numeric਀匀䔀䰀䔀䌀吀
  seqval AS cur,਀  ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
   FROM dbo.NumSeq AS B਀   圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀 䄀⸀猀攀焀瘀愀氀⤀ 䄀匀 渀砀琀
FROM dbo.NumSeq AS A;਀
SELECT cur + 1 AS start_range, nxt - 1 AS end_range਀䘀刀伀䴀 ⠀匀䔀䰀䔀䌀吀
        seqval AS cur,਀        ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
         FROM dbo.NumSeq AS B਀         圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀 䄀⸀猀攀焀瘀愀氀⤀ 䄀匀 渀砀琀
      FROM dbo.NumSeq AS A) AS D਀圀䠀䔀刀䔀 渀砀琀 ⴀ 挀甀爀 㸀 ㄀㬀
਀ⴀⴀ 䈀椀最
-- 48 seconds, 31,875,478 logical reads਀匀䔀䰀䔀䌀吀 挀甀爀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 渀砀琀 ⴀ ㄀ 䄀匀 攀渀搀开爀愀渀最攀
FROM (SELECT਀        猀攀焀瘀愀氀 䄀匀 挀甀爀Ⰰ
        (SELECT MIN(B.seqval)਀         䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䈀
         WHERE B.seqval > A.seqval) AS nxt਀      䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䄀⤀ 䄀匀 䐀
WHERE nxt - cur > 1;਀
-- Temporal਀匀䔀䰀䔀䌀吀 
  DATEADD(hour, 4, cur) AS start_range,਀  䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ ⴀ㐀Ⰰ 渀砀琀⤀ 䄀匀 攀渀搀开爀愀渀最攀
FROM (SELECT਀        猀攀焀瘀愀氀 䄀匀 挀甀爀Ⰰ
        (SELECT MIN(B.seqval)਀         䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䈀
         WHERE B.seqval > A.seqval) AS nxt਀      䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䄀⤀ 䄀匀 䐀
WHERE DATEDIFF(hour, cur, nxt) > 4;਀
-- Dups਀匀䔀䰀䔀䌀吀 挀甀爀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 渀砀琀 ⴀ ㄀ 䄀匀 攀渀搀开爀愀渀最攀
FROM (SELECT਀        猀攀焀瘀愀氀 䄀匀 挀甀爀Ⰰ
        (SELECT MIN(B.seqval)਀         䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䈀
         WHERE B.seqval > A.seqval) AS nxt਀      䘀刀伀䴀 ⠀匀䔀䰀䔀䌀吀 䐀䤀匀吀䤀一䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⤀ 䄀匀 䄀⤀ 䄀匀 䐀
WHERE nxt - cur > 1;਀
SELECT DISTINCT cur + 1 AS start_range, nxt - 1 AS end_range਀䘀刀伀䴀 ⠀匀䔀䰀䔀䌀吀
        seqval AS cur,਀        ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
         FROM dbo.NumSeqDups AS B਀         圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀 䄀⸀猀攀焀瘀愀氀⤀ 䄀匀 渀砀琀
      FROM dbo.NumSeqDups AS A) AS D਀圀䠀䔀刀䔀 渀砀琀 ⴀ 挀甀爀 㸀 ㄀㬀
਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
-- Gaps, Solution 3 using ranking functions਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
਀ⴀⴀ 一甀洀攀爀椀挀
WITH C AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀
)਀匀䔀䰀䔀䌀吀 䌀甀爀⸀猀攀焀瘀愀氀 ⬀ ㄀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 一砀琀⸀猀攀焀瘀愀氀 ⴀ ㄀ 䄀匀 攀渀搀开爀愀渀最攀
FROM C AS Cur਀  䨀伀䤀一 䌀 䄀匀 一砀琀
    ON Nxt.rownum = Cur.rownum + 1਀圀䠀䔀刀䔀 一砀琀⸀猀攀焀瘀愀氀 ⴀ 䌀甀爀⸀猀攀焀瘀愀氀 㸀 ㄀㬀
਀ⴀⴀ 䈀椀最
-- 24 seconds, 32,246 logical reads਀圀䤀吀䠀 䌀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM dbo.BigNumSeq਀⤀
SELECT Cur.seqval + 1 AS start_range, Nxt.seqval - 1 AS end_range਀䘀刀伀䴀 䌀 䄀匀 䌀甀爀
  JOIN C AS Nxt਀    伀一 一砀琀⸀爀漀眀渀甀洀 㴀 䌀甀爀⸀爀漀眀渀甀洀 ⬀ ㄀
WHERE Nxt.seqval - Cur.seqval > 1;਀
-- Temporal਀圀䤀吀䠀 䌀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM dbo.TempSeq਀⤀
SELECT਀  䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ 㐀Ⰰ 䌀甀爀⸀猀攀焀瘀愀氀⤀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ
  DATEADD(hour, -4, Nxt.Seqval) AS end_range਀䘀刀伀䴀 䌀 䄀匀 䌀甀爀
  JOIN C AS Nxt਀    伀一 一砀琀⸀爀漀眀渀甀洀 㴀 䌀甀爀⸀爀漀眀渀甀洀 ⬀ ㄀
WHERE DATEDIFF(hour, Cur.seqval, Nxt.seqval) > 4;਀
-- Dups਀圀䤀吀䠀 䌀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM (SELECT DISTINCT seqval FROM dbo.NumSeqDups) AS D਀⤀
SELECT Cur.seqval + 1 AS start_range, Nxt.seqval - 1 AS end_range਀䘀刀伀䴀 䌀 䄀匀 䌀甀爀
  JOIN C AS Nxt਀    伀一 一砀琀⸀爀漀眀渀甀洀 㴀 䌀甀爀⸀爀漀眀渀甀洀 ⬀ ㄀
WHERE Nxt.seqval - Cur.seqval > 1;਀
WITH C1 AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(PARTITION BY seqval਀                                   伀刀䐀䔀刀 䈀夀 ⠀匀䔀䰀䔀䌀吀 　⤀⤀ 䄀匀 搀甀瀀渀甀洀
  FROM dbo.NumSeqDups਀⤀Ⰰ
C2 AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 䌀㄀
  WHERE dupnum = 1਀⤀
SELECT Cur.seqval + 1 AS start_range, Nxt.seqval - 1 AS end_range਀䘀刀伀䴀 䌀㈀ 䄀匀 䌀甀爀
  JOIN C2 AS Nxt਀    伀一 一砀琀⸀爀漀眀渀甀洀 㴀 䌀甀爀⸀爀漀眀渀甀洀 ⬀ ㄀
WHERE Nxt.seqval - Cur.seqval > 1;਀
----------------------------------------------------------------------਀ⴀⴀ 䜀愀瀀猀Ⰰ 匀漀氀甀琀椀漀渀 㐀 甀猀椀渀最 挀甀爀猀漀爀猀
----------------------------------------------------------------------਀
-- 250 seconds, 16,123 logical reads਀匀䔀吀 一伀䌀伀唀一吀 伀一㬀
਀䐀䔀䌀䰀䄀刀䔀 䀀猀攀焀瘀愀氀 䄀匀 䤀一吀Ⰰ 䀀瀀爀瘀猀攀焀瘀愀氀 䄀匀 䤀一吀㬀
DECLARE @Gaps TABLE(start_range INT, end_range INT);਀
DECLARE C CURSOR FAST_FORWARD FOR਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀㬀
਀伀倀䔀一 䌀㬀
਀䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 䌀 䤀一吀伀 䀀瀀爀瘀猀攀焀瘀愀氀㬀
IF @@FETCH_STATUS = 0 FETCH NEXT FROM C INTO @seqval;਀
WHILE @@FETCH_STATUS = 0਀䈀䔀䜀䤀一
  IF @seqval - @prvseqval > 1਀    䤀一匀䔀刀吀 䤀一吀伀 䀀䜀愀瀀猀⠀猀琀愀爀琀开爀愀渀最攀Ⰰ 攀渀搀开爀愀渀最攀⤀
      VALUES(@prvseqval + 1, @seqval - 1);਀
  SET @prvseqval = @seqval;਀  䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 䌀 䤀一吀伀 䀀猀攀焀瘀愀氀㬀
END਀
CLOSE C;਀
DEALLOCATE C;਀
SELECT start_range, end_range FROM @Gaps;਀
----------------------------------------------------------------------਀ⴀⴀ 刀攀琀甀爀渀椀渀最 䤀渀搀椀瘀椀搀甀愀氀 䴀椀猀猀椀渀最 嘀愀氀甀攀猀
----------------------------------------------------------------------਀
SELECT n FROM dbo.Nums਀圀䠀䔀刀䔀 渀 䈀䔀吀圀䔀䔀一 ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀猀攀焀瘀愀氀⤀ 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀⤀
            AND (SELECT MAX(seqval) FROM dbo.NumSeq)਀  䄀一䐀 渀 一伀吀 䤀一⠀匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀⤀㬀
਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
-- Islands, Solution 1 using subqueries and ranking calculations਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
਀ⴀⴀ 一甀洀攀爀椀挀
WITH StartingPoints AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䄀
  WHERE NOT EXISTS਀    ⠀匀䔀䰀䔀䌀吀 ⨀
     FROM dbo.NumSeq AS B਀     圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⴀ ㄀⤀
),਀䔀渀搀椀渀最倀漀椀渀琀猀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM dbo.NumSeq AS A਀  圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀
    (SELECT *਀     䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
     WHERE B.seqval = A.seqval + 1)਀⤀
SELECT S.seqval AS start_range, E.seqval AS end_range਀䘀刀伀䴀 匀琀愀爀琀椀渀最倀漀椀渀琀猀 䄀匀 匀
  JOIN EndingPoints AS E਀    伀一 䔀⸀爀漀眀渀甀洀 㴀 匀⸀爀漀眀渀甀洀㬀
਀ⴀⴀ 䈀椀最
-- 17 seconds, 64,492 logical reads਀圀䤀吀䠀 匀琀愀爀琀椀渀最倀漀椀渀琀猀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM dbo.BigNumSeq AS A਀  圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀
    (SELECT *਀     䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䈀
     WHERE B.seqval = A.seqval - 1)਀⤀Ⰰ
EndingPoints AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䄀
  WHERE NOT EXISTS਀    ⠀匀䔀䰀䔀䌀吀 ⨀
     FROM dbo.BigNumSeq AS B਀     圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⬀ ㄀⤀
)਀匀䔀䰀䔀䌀吀 匀⸀猀攀焀瘀愀氀 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䔀⸀猀攀焀瘀愀氀 䄀匀 攀渀搀开爀愀渀最攀
FROM StartingPoints AS S਀  䨀伀䤀一 䔀渀搀椀渀最倀漀椀渀琀猀 䄀匀 䔀
    ON E.rownum = S.rownum;਀
-- Temporal਀圀䤀吀䠀 匀琀愀爀琀椀渀最倀漀椀渀琀猀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM dbo.TempSeq AS A਀  圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀
    (SELECT *਀     䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䈀
     WHERE B.seqval = DATEADD(hour, -4, A.seqval))਀⤀Ⰰ
EndingPoints AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 搀戀漀⸀吀攀洀瀀匀攀焀 䄀匀 䄀
  WHERE NOT EXISTS਀    ⠀匀䔀䰀䔀䌀吀 ⨀
     FROM dbo.TempSeq AS B਀     圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ 㐀Ⰰ 䄀⸀猀攀焀瘀愀氀⤀⤀
)਀匀䔀䰀䔀䌀吀 匀⸀猀攀焀瘀愀氀 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䔀⸀猀攀焀瘀愀氀 䄀匀 攀渀搀开爀愀渀最攀
FROM StartingPoints AS S਀  䨀伀䤀一 䔀渀搀椀渀最倀漀椀渀琀猀 䄀匀 䔀
    ON E.rownum = S.rownum;਀
-- Dups਀圀䤀吀䠀 匀琀愀爀琀椀渀最倀漀椀渀琀猀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 爀漀眀渀甀洀
  FROM (SELECT DISTINCT seqval FROM dbo.NumSeqDups) AS A਀  圀䠀䔀刀䔀 一伀吀 䔀堀䤀匀吀匀
    (SELECT *਀     䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䈀
     WHERE B.seqval = A.seqval - 1)਀⤀Ⰰ
EndingPoints AS਀⠀
  SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀  䘀刀伀䴀 ⠀匀䔀䰀䔀䌀吀 䐀䤀匀吀䤀一䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀⤀ 䄀匀 䄀
  WHERE NOT EXISTS਀    ⠀匀䔀䰀䔀䌀吀 ⨀
     FROM dbo.NumSeqDups AS B਀     圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㴀 䄀⸀猀攀焀瘀愀氀 ⬀ ㄀⤀
)਀匀䔀䰀䔀䌀吀 匀⸀猀攀焀瘀愀氀 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䔀⸀猀攀焀瘀愀氀 䄀匀 攀渀搀开爀愀渀最攀
FROM StartingPoints AS S਀  䨀伀䤀一 䔀渀搀椀渀最倀漀椀渀琀猀 䄀匀 䔀
    ON E.rownum = S.rownum;਀
----------------------------------------------------------------------਀ⴀⴀ 䤀猀氀愀渀搀猀Ⰰ 匀漀氀甀琀椀漀渀 ㈀ 甀猀椀渀最 最爀漀甀瀀 椀搀攀渀琀椀昀椀攀爀 戀愀猀攀搀 漀渀 猀甀戀焀甀攀爀椀攀猀
----------------------------------------------------------------------਀
-- Numeric਀匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ
  (SELECT MIN(B.seqval)਀   䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
   WHERE B.seqval >= A.seqval਀     䄀一䐀 一伀吀 䔀堀䤀匀吀匀
       (SELECT *਀        䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䌀
        WHERE C.seqval = B.seqval + 1)) AS grp਀䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䄀㬀
਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ
    (SELECT MIN(B.seqval)਀     䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䈀
     WHERE B.seqval >= A.seqval਀       䄀一䐀 一伀吀 䔀堀䤀匀吀匀
         (SELECT *਀          䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䌀
          WHERE C.seqval = B.seqval + 1)) AS grp਀  䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀 䄀匀 䄀
)਀匀䔀䰀䔀䌀吀 䴀䤀一⠀猀攀焀瘀愀氀⤀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䄀匀 攀渀搀开爀愀渀最攀
FROM D਀䜀刀伀唀倀 䈀夀 最爀瀀㬀
਀ⴀⴀ 䈀椀最
-- aborted execution after 600 seconds਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ
    (SELECT MIN(B.seqval)਀     䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䈀
     WHERE B.seqval >= A.seqval਀       䄀一䐀 一伀吀 䔀堀䤀匀吀匀
         (SELECT *਀          䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䌀
          WHERE C.seqval = B.seqval + 1)) AS grp਀  䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 䄀匀 䄀
)਀匀䔀䰀䔀䌀吀 䴀䤀一⠀猀攀焀瘀愀氀⤀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䄀匀 攀渀搀开爀愀渀最攀
FROM D਀䜀刀伀唀倀 䈀夀 最爀瀀㬀
਀ⴀⴀ 吀攀洀瀀漀爀愀氀
WITH D AS਀⠀
  SELECT seqval,਀    ⠀匀䔀䰀䔀䌀吀 䴀䤀一⠀䈀⸀猀攀焀瘀愀氀⤀
     FROM dbo.TempSeq AS B਀     圀䠀䔀刀䔀 䈀⸀猀攀焀瘀愀氀 㸀㴀 䄀⸀猀攀焀瘀愀氀
       AND NOT EXISTS਀         ⠀匀䔀䰀䔀䌀吀 ⨀
          FROM dbo.TempSeq AS C਀          圀䠀䔀刀䔀 䌀⸀猀攀焀瘀愀氀 㴀 䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ 㐀Ⰰ 䈀⸀猀攀焀瘀愀氀⤀⤀⤀ 䄀匀 最爀瀀
  FROM dbo.TempSeq AS A਀⤀
SELECT MIN(seqval) AS start_range, MAX(seqval) AS end_range਀䘀刀伀䴀 䐀
GROUP BY grp;਀
-- Dups਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ
    (SELECT MIN(B.seqval)਀     䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䈀
     WHERE B.seqval >= A.seqval਀       䄀一䐀 一伀吀 䔀堀䤀匀吀匀
         (SELECT *਀          䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䌀
          WHERE C.seqval = B.seqval + 1)) AS grp਀  䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀䐀甀瀀猀 䄀匀 䄀
)਀匀䔀䰀䔀䌀吀 䴀䤀一⠀猀攀焀瘀愀氀⤀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䄀匀 攀渀搀开爀愀渀最攀
FROM D਀䜀刀伀唀倀 䈀夀 最爀瀀㬀
਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
-- Islands, Solution 3 using group identifier based on ranking calculations਀ⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀⴀ
਀ⴀⴀ 一甀洀攀爀椀挀
SELECT seqval, ROW_NUMBER() OVER(ORDER BY seqval) AS rownum਀䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀㬀
਀匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 猀攀焀瘀愀氀 ⴀ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 搀椀昀昀
FROM dbo.NumSeq;਀
WITH D AS਀⠀
  SELECT seqval, seqval - ROW_NUMBER() OVER(ORDER BY seqval) AS grp਀  䘀刀伀䴀 搀戀漀⸀一甀洀匀攀焀
)਀匀䔀䰀䔀䌀吀 䴀䤀一⠀猀攀焀瘀愀氀⤀ 䄀匀 猀琀愀爀琀开爀愀渀最攀Ⰰ 䴀䄀堀⠀猀攀焀瘀愀氀⤀ 䄀匀 攀渀搀开爀愀渀最攀
FROM D਀䜀刀伀唀倀 䈀夀 最爀瀀㬀
਀ⴀⴀ 䈀椀最
-- 10 seconds, 16,123 logical reads਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 猀攀焀瘀愀氀 ⴀ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 最爀瀀
  FROM dbo.BigNumSeq਀⤀
SELECT MIN(seqval) AS start_range, MAX(seqval) AS end_range਀䘀刀伀䴀 䐀
GROUP BY grp;਀
-- Temporal਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 䐀䄀吀䔀䄀䐀䐀⠀栀漀甀爀Ⰰ ⴀ㐀 ⨀ 刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀Ⰰ 猀攀焀瘀愀氀⤀ 䄀匀 最爀瀀
  FROM dbo.TempSeq਀⤀
SELECT MIN(seqval) AS start_range, MAX(seqval) AS end_range਀䘀刀伀䴀 䐀
GROUP BY grp;਀
-- Dups਀圀䤀吀䠀 䐀 䄀匀
(਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀Ⰰ 猀攀焀瘀愀氀 ⴀ 䐀䔀一匀䔀开刀䄀一䬀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀⤀ 䄀匀 最爀瀀
  FROM dbo.NumSeqDups਀⤀
SELECT MIN(seqval) AS start_range, MAX(seqval) AS end_range਀䘀刀伀䴀 䐀
GROUP BY grp;਀
----------------------------------------------------------------------਀ⴀⴀ 䤀猀氀愀渀搀猀Ⰰ 匀漀氀甀琀椀漀渀 㐀 甀猀椀渀最 挀甀爀猀漀爀猀
----------------------------------------------------------------------਀
-- 217 seconds, 16,123 logical reads਀匀䔀吀 一伀䌀伀唀一吀 伀一㬀
਀䐀䔀䌀䰀䄀刀䔀 䀀猀攀焀瘀愀氀 䄀匀 䤀一吀Ⰰ 䀀瀀爀瘀猀攀焀瘀愀氀 䄀匀 䤀一吀Ⰰ 䀀昀椀爀猀琀 䄀匀 䤀一吀㬀
DECLARE @Islands TABLE(start_range INT, end_range INT);਀
DECLARE C CURSOR FAST_FORWARD FOR਀  匀䔀䰀䔀䌀吀 猀攀焀瘀愀氀 䘀刀伀䴀 搀戀漀⸀䈀椀最一甀洀匀攀焀 伀刀䐀䔀刀 䈀夀 猀攀焀瘀愀氀㬀
਀伀倀䔀一 䌀㬀
਀䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 䌀 䤀一吀伀 䀀猀攀焀瘀愀氀㬀
SET @first = @seqval;਀匀䔀吀 䀀瀀爀瘀猀攀焀瘀愀氀 㴀 䀀猀攀焀瘀愀氀㬀
਀圀䠀䤀䰀䔀 䀀䀀䘀䔀吀䌀䠀开匀吀䄀吀唀匀 㴀 　
BEGIN਀  䤀䘀 䀀猀攀焀瘀愀氀 ⴀ 䀀瀀爀瘀猀攀焀瘀愀氀 㸀 ㄀
  BEGIN਀    䤀一匀䔀刀吀 䤀一吀伀 䀀䤀猀氀愀渀搀猀⠀猀琀愀爀琀开爀愀渀最攀Ⰰ 攀渀搀开爀愀渀最攀⤀
      VALUES(@first, @prvseqval);਀    匀䔀吀 䀀昀椀爀猀琀 㴀 䀀猀攀焀瘀愀氀㬀
  END਀  
  SET @prvseqval = @seqval;਀  䘀䔀吀䌀䠀 一䔀堀吀 䘀刀伀䴀 䌀 䤀一吀伀 䀀猀攀焀瘀愀氀㬀
END਀
IF @first IS NOT NULL਀  䤀一匀䔀刀吀 䤀一吀伀 䀀䤀猀氀愀渀搀猀⠀猀琀愀爀琀开爀愀渀最攀Ⰰ 攀渀搀开爀愀渀最攀⤀
    VALUES(@first, @prvseqval);਀  
CLOSE C;਀
DEALLOCATE C;਀
SELECT start_range, end_range FROM @Islands;਀
----------------------------------------------------------------------਀ⴀⴀ 嘀愀爀椀愀琀椀漀渀 漀昀 䤀猀氀愀渀搀猀 倀爀漀戀氀攀洀
----------------------------------------------------------------------਀
USE tempdb;਀䤀䘀 伀䈀䨀䔀䌀吀开䤀䐀⠀✀搀戀漀⸀吀㌀✀⤀ 䤀匀 一伀吀 一唀䰀䰀 䐀刀伀倀 吀䄀䈀䰀䔀 搀戀漀⸀吀㄀㬀
CREATE TABLE dbo.T3਀⠀
  id  INT         NOT NULL PRIMARY KEY,਀  瘀愀氀 嘀䄀刀䌀䠀䄀刀⠀㄀　⤀ 一伀吀 一唀䰀䰀
);਀䜀伀
਀䤀一匀䔀刀吀 䤀一吀伀 搀戀漀⸀吀㌀⠀椀搀Ⰰ 瘀愀氀⤀ 嘀䄀䰀唀䔀匀
  (2, 'a'),਀  ⠀㌀Ⰰ ✀愀✀⤀Ⰰ
  (5, 'a'),਀  ⠀㜀Ⰰ ✀戀✀⤀Ⰰ
  (11, 'b'),਀  ⠀㄀㌀Ⰰ ✀愀✀⤀Ⰰ
  (17, 'a'),਀  ⠀㄀㤀Ⰰ ✀愀✀⤀Ⰰ
  (23, 'c'),਀  ⠀㈀㤀Ⰰ ✀挀✀⤀Ⰰ
  (31, 'a'),਀  ⠀㌀㜀Ⰰ ✀愀✀⤀Ⰰ
  (41, 'a'),਀  ⠀㐀㌀Ⰰ ✀愀✀⤀Ⰰ
  (47, 'c'),਀  ⠀㔀㌀Ⰰ ✀挀✀⤀Ⰰ
  (59, 'c');਀
SELECT id, val,਀  刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 椀搀⤀ 䄀匀 爀渀开椀搀Ⰰ
  ROW_NUMBER() OVER(ORDER BY val, id) AS rn_val_id਀䘀刀伀䴀 搀戀漀⸀吀㌀
ORDER BY id;਀
SELECT id, val,਀  刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 椀搀⤀
    - ROW_NUMBER() OVER(ORDER BY val, id) AS diff਀䘀刀伀䴀 搀戀漀⸀吀㌀
ORDER BY id;਀
WITH C AS਀⠀
  SELECT id, val,਀    刀伀圀开一唀䴀䈀䔀刀⠀⤀ 伀嘀䔀刀⠀伀刀䐀䔀刀 䈀夀 椀搀⤀
      - ROW_NUMBER() OVER(ORDER BY val, id) AS grp਀  䘀刀伀䴀 搀戀漀⸀吀㌀
)਀匀䔀䰀䔀䌀吀 䴀䤀一⠀椀搀⤀ 䄀匀 洀渀Ⰰ 䴀䄀堀⠀椀搀⤀ 䄀匀 洀砀Ⰰ 瘀愀氀
FROM C਀䜀刀伀唀倀 䈀夀 瘀愀氀Ⰰ 最爀瀀
ORDER BY mn;਀਀