我在mssql中有下面這樣一張資料庫表，表格內容是這樣的：
中間存在斷號，我想通過寫sql陳述句分組把中間的斷號查出來，得到下列的結果,各位大神有什么好的方法，請指導一下，謝謝：
uj5u.com熱心網友回復：
CREATE TABLE #A
(
全宗號 VARCHAR(10),
目錄號 VARCHAR(10),
案卷號 INT
)
INSERT INTO #A VALUES('A001','001',1)
INSERT INTO #A VALUES('A001','001',2)
INSERT INTO #A VALUES('A001','001',6)
INSERT INTO #A VALUES('A001','001',7)
INSERT INTO #A VALUES('A001','001',8)
INSERT INTO #A VALUES('A001','001',10)
INSERT INTO #A VALUES('A001','001',12)
INSERT INTO #A VALUES('A001','002',1)
INSERT INTO #A VALUES('A001','002',2)
INSERT INTO #A VALUES('A001','002',4)
INSERT INTO #A VALUES('A001','002',5)
;
WITH ct
AS
(
SELECT 全宗號,目錄號,MIN(案卷號) AS mi ,MAX(案卷號) AS ma FROM
(
SELECT *,案卷號-ROW_NUMBER() OVER( PARTITION BY 全宗號,目錄號 ORDER BY 案卷號 ) AS idx FROM #A
) S
GROUP BY 全宗號,目錄號,idx
)
SELECT 全宗號,目錄號,STUFF(
(
SELECT ','+CASE WHEN ct.ma=mi THEN CONVERT(VARCHAR(10),ct.mi) ELSE CONVERT(VARCHAR(10),ct.mi)+'-'+CONVERT(VARCHAR(10),ct.ma) END
FROM ct
WHERE 全宗號=a.全宗號 AND 目錄號=a.目錄號
ORDER BY mi
FOR XML PATH('')),1,1,''
) AS 缺號區間
FROM ct a
GROUP BY 全宗號,目錄號
DROP TABLE #A
uj5u.com熱心網友回復：
CREATE TABLE #A(
ID INT,
NUM INT
)
INSERT INTO #A VALUES (1,1),(1,2),(1,3),(1,6),(2,1),(2,5),(2,10),(2,11)
SELECT XX.ID,
STUFF((SELECT ','+XXX.缺號
FROM (
SELECT XX1.ID,CAST(XX2.NUM+1 AS VARCHAR(10))+'-'+CAST(XX1.NUM-1 AS VARCHAR(10)) 缺號
FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM) ROWID FROM #A A)XX1,
(SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM) ROWID FROM #A A)XX2
WHERE XX1.ID = XX2.ID AND XX1.ROWID = XX2.ROWID+1 AND (XX1.NUM-XX2.NUM)!=1 AND XX1.ID = XX.ID)XXX
FOR XML PATH ('')),1,1,'') 缺號區間
FROM (
SELECT XX1.ID,CAST(XX2.NUM+1 AS VARCHAR(10))+'-'+CAST(XX1.NUM-1 AS VARCHAR(10)) 缺號
FROM
(SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM) ROWID FROM #A A)XX1,
(SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NUM) ROWID FROM #A A)XX2
WHERE XX1.ID = XX2.ID AND XX1.ROWID = XX2.ROWID+1 AND (XX1.NUM-XX2.NUM)!=1 )XX
GROUP BY XX.ID
DROP TABLE #A
等大佬優化一下
uj5u.com熱心網友回復：
CREATE TABLE #T
(
全宗號 VARCHAR(10),
目錄號 VARCHAR(10),
案卷號 INT
)
INSERT INTO #T VALUES('A001','001',3)
INSERT INTO #T VALUES('A001','001',5)
INSERT INTO #T VALUES('A001','001',6)
INSERT INTO #T VALUES('A001','001',7)
INSERT INTO #T VALUES('A001','001',10)
INSERT INTO #T VALUES('A001','001',12)
INSERT INTO #T VALUES('A001','001',20)
INSERT INTO #T VALUES('A001','001',21)
INSERT INTO #T VALUES('A001','001',8)
INSERT INTO #T VALUES('A001','001',30)
INSERT INTO #T VALUES('A001','002',1)
INSERT INTO #T VALUES('A001','002',2)
INSERT INTO #T VALUES('A001','002',4)
INSERT INTO #T VALUES('A001','002',5)
INSERT INTO #T VALUES('A001','002',10)
INSERT INTO #T VALUES('A001','002',11)
INSERT INTO #T VALUES('A001','002',16)
WITH CTE_1
AS
(SELECT A.全宗號,目錄號,B.number FROM
(SELECT 全宗號,目錄號,MAX(案卷號) AS END_NO
FROM #T
GROUP BY 全宗號,目錄號) AS A
JOIN MASTER.DBO.SPT_VALUES B ON B.number<=A.END_NO
WHERE TYPE='P' AND number>0),
CTE_2
AS
(SELECT 全宗號,目錄號,
CASE WHEN MIN(NUMBER)=MAX(NUMBER) THEN CAST(MIN(NUMBER) AS VARCHAR) ELSE CAST(MIN(NUMBER) AS VARCHAR)+'-'+CAST(MAX(NUMBER) AS VARCHAR) END AS NO_GAP,
MIN(NUMBER) AS SORT
FROM
(SELECT A.*,ROW_NUMBER() OVER (PARTITION BY A.全宗號,A.目錄號 ORDER BY NUMBER) AS RN
FROM CTE_1 A
LEFT JOIN #T B ON A.目錄號=B.目錄號 AND A.全宗號=B.全宗號 AND A.number=B.案卷號
WHERE B.全宗號 IS NULL) AS C
GROUP BY 全宗號,目錄號,RN-NUMBER)
SELECT 全宗號,目錄號,STUFF((SELECT ','+NO_GAP FROM CTE_2 WHERE A.目錄號=目錄號 AND A.全宗號=全宗號 ORDER BY SORT FOR XML PATH ('')),1,1,'') AS NO_GAP
FROM CTE_2 AS A
GROUP BY 全宗號,目錄號
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/248329.html
標籤：應用實例
上一篇：多執行緒操作資料庫，偶發失效
下一篇：求問