我正在嘗試創建一個程式，它將：
- 以任何數字作為輸入，例如
102 - 找到它所屬的序列范圍，例如
100 to 103 - 向用戶回傳一個建議的下一個數字，例如
104
表格本身看起來像這樣：
| 數量 |
|---|
| 100 |
| 101 |
| 102 |
| 103 |
| 110 |
| 111 |
| 112 |
| 113 |
| 114 |
| 115 |
| 120 |
| 121 |
理想情況下，查詢的輸出將回傳如下內容：
| 開始 | 結尾 | 下一個編號 |
|---|---|---|
| 100 | 103 | 104 |
| 110 | 115 | 116 |
| 120 | 121 | 122 |
我這就是我想要做的與某種間隙和島嶼技術有關。我想從這里嘗試一些東西，但無法讓它發揮作用。 縫隙和島嶼鏈接
這就是我試圖想出的......
WITH cteSource(ID, Seq, Num)
AS(
SELECT d.ID, f.Seq, f.Num
FROM (
SELECT
ID,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
MIN(SeqNo) AS StartSeqNo,
MAX(SeqNo) AS EndSeqNo
FROM
(
SELECT 1 ID, Num SeqNo,
Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
FROM
Number
) AS a
GROUP BY ID, RN
) d
CROSS APPLY (
VALUES (d.Grp, d.EndSeqNo 1),(d.Grp -1, d.StartSeqNo -1)
) AS f(Seq, Num)
)
SELECT ID, MIN(Num) AS StartSeqNo, MAX(Num) AS EndSeqNo, MAX(Num) 1 as NextNr
FROM cteSource
GROUP BY ID, Seq
HAVING COUNT(*) = 2
結果如下所示：
| 起始序列號 | 結束序列號 | 下一編號 |
|---|---|---|
| 104 | 109 | 110 |
| 116 | 119 | 120 |
這是設定：
CREATE TABLE [dbo].[Number](
[Num] [int] NULL
)
GO
INSERT INTO Number
(Num)
VALUES
(100),
(101),
(102),
(103),
(110),
(111),
(112),
(113),
(114),
(115),
(120),
(121)
uj5u.com熱心網友回復：
也許這會有所幫助。
Select [Start] = min(num)
,[End] = max(num)
,[NextNr] = max(num) 1
From (
Select *
,Grp = num - row_number() over (order by num)
From number
) A
Group By Grp
結果
Start End NextNr
100 103 104
110 115 116
120 121 122
uj5u.com熱心網友回復：
您的第一個子查詢可以很好地獲取組以及開始和結束編號：
SELECT
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
MIN(SeqNo) AS StartSeqNo,
MAX(SeqNo) AS EndSeqNo
FROM
(
SELECT 1 ID, Num SeqNo,
Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
FROM
Number
) AS a
GROUP BY ID, RN
然后你不必要地將它復雜化，而不是僅僅對 NextNr 使用 EndSeqNo 1：
WITH CTE_Groups AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MIN(SeqNo)) AS Grp,
MIN(SeqNo) AS StartSeqNo,
MAX(SeqNo) AS EndSeqNo
FROM
(
SELECT 1 ID, Num SeqNo,
Num - ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY Num) AS RN
FROM
Number
) AS a
GROUP BY ID, RN
)
SELECT *, EndSeqNo 1 AS NextNr
FROM
CTE_Groups
編輯：并進一步洗掉不必要的磁區和額外的列
WITH CTE_Groups AS
(
SELECT
MIN(SeqNo) AS StartSeqNo,
MAX(SeqNo) AS EndSeqNo
FROM
(
SELECT Num SeqNo,
Num - ROW_NUMBER() OVER (ORDER BY Num) AS RN
FROM
Number
) AS a
GROUP BY RN
)
SELECT *, EndSeqNo 1 AS NextNr
FROM
CTE_Groups
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379321.html
標籤：sql sql-server