insert into Temp('A1000001')
insert into Temp('A1000002')
insert into Temp('A1000003')
insert into Temp('A1000004')
insert into Temp('A1000006')
insert into Temp('A1000007')
insert into Temp('A1000009')
insert into Temp('A10000010')
insert into Temp('A10000011')
insert into Temp('A10000012')
如上邊資料，我想把資料集合在一個字串中輸出，如果有斷號用,表示，如果沒有斷號顯示起始號-終止號
上邊的資料輸出結果應該為：A1000001-A1000004,A1000006-A1000007,A1000009-A1000012
uj5u.com熱心網友回復：
CREATE TABLE #Temp
(
id INT IDENTITY(1,1),
val VARCHAR(50)
)
INSERT into #Temp(val) values('A1000001')
insert into #Temp(val) values('A1000002')
insert into #Temp(val) values('A1000003')
insert into #Temp(val) values('A1000004')
insert into #Temp(val) values('A1000006')
insert into #Temp(val) values('A1000009')
insert into #Temp(val) values('A10000010')
insert into #Temp(val) values('A10000011')
insert into #Temp(val) values('A10000012')
SELECT STUFF(
(
SELECT ','+mi+CASE WHEN mi=mx THEN '' else '-'+ mx END FROM
(
SELECT MIN(val) AS mi,MAX(val) AS mx,MIN(id) AS id FROM
(
SELECT * , id - CONVERT (INT,SUBSTRING(val,3,100)) AS x
FROM #Temp
) a
GROUP BY a.x
) b
ORDER BY id
FOR XML PATH('')
),1,1,'')
DROP TABLE #Temp
uj5u.com熱心網友回復：
--我暈,你的資料位數不一樣
CREATE TABLE #Temp
(
id INT IDENTITY(1,1),
val VARCHAR(50)
)
INSERT into #Temp(val) values('A10000001')
insert into #Temp(val) values('A10000002')
insert into #Temp(val) values('A10000003')
insert into #Temp(val) values('A10000004')
insert into #Temp(val) values('A10000006')
insert into #Temp(val) values('A10000009')
insert into #Temp(val) values('A10000010')
insert into #Temp(val) values('A10000011')
insert into #Temp(val) values('A10000012')
SELECT STUFF(
(
SELECT ','+mi+CASE WHEN mi=mx THEN '' else '-'+ mx END FROM
(
SELECT MIN(val) AS mi,MAX(val) AS mx,MIN(id) AS id FROM
(
SELECT * , id - CONVERT (INT,SUBSTRING(val,3,100)) AS x
FROM #Temp
) a
GROUP BY a.x
) b
ORDER BY id
FOR XML PATH('')
),1,1,'')
DROP TABLE #Temp
uj5u.com熱心網友回復：
大神，給跪了
uj5u.com熱心網友回復：
有一個缺陷就是沒有做val的排序，如果順序亂掉的話就沒辦法了
uj5u.com熱心網友回復：
--沒順序不怕,關鍵是你的資料整齊不,你確定一下,我是看整齊的那種來做的
CREATE TABLE #Temp
(
val VARCHAR(50)
)
insert into #Temp(val) values('A10000003')
INSERT into #Temp(val) values('A10000001')
insert into #Temp(val) values('A10000002')
insert into #Temp(val) values('A10000011')
insert into #Temp(val) values('A10000004')
insert into #Temp(val) values('A10000006')
insert into #Temp(val) values('A10000009')
insert into #Temp(val) values('A10000010')
insert into #Temp(val) values('A10000012')
SELECT STUFF(
(
SELECT ','+mi+CASE WHEN mi=mx THEN '' else '-'+ mx END FROM
(
SELECT MIN(val) AS mi,MAX(val) AS mx,MIN(id) AS id FROM
(
SELECT * , id - CONVERT (INT,SUBSTRING(val,3,100)) AS x
FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY CONVERT (INT,SUBSTRING(val,3,100))) AS id FROM #Temp) y
) a
GROUP BY a.x
) b
ORDER BY id
FOR XML PATH('')
),1,1,'')
DROP TABLE #Temp
uj5u.com熱心網友回復：
存盤資料不規范，效率低A1000009-->A10000009
A10000010
uj5u.com熱心網友回復：
e.g.USE Test
GO
DROP TABLE #Temp;
CREATE TABLE #Temp
(
id INT IDENTITY(1,1),
val VARCHAR(50)
);
insert into #Temp values('A1000001')
insert into #Temp values('A1000002')
insert into #Temp values('A1000003')
insert into #Temp values('A1000004')
insert into #Temp values('A1000006')
insert into #Temp values('A1000007')
insert into #Temp values('A1000009')
insert into #Temp values('A10000010')
insert into #Temp values('A10000011')
insert into #Temp values('A10000012')
GO
SELECT STUFF((SELECT CASE WHEN id=MIN(id)OVER(PARTITION BY Grp) THEN ','+val+'-' ELSE '' END+CASE WHEN id=MAX(id)OVER(PARTITION BY Grp) THEN val ELSE '' END from(SELECT id,val,ROW_NUMBER()OVER(ORDER BY id) -RIGHT(val,LEN(val)-2)*1 AS Grp FROM #Temp) AS t ORDER BY id FOR XML PATH('')),1,1,'')
/*
A1000001-A1000004,A1000006-A1000007,A1000009-A10000012
*/
uj5u.com熱心網友回復：
經典的斷號查詢需求。轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/172882.html
標籤：疑難問題
上一篇：資料庫