22,210
社区成员
发帖
与我相关
我的任务
分享求一个sql语句
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
...全文
请发表友善的回复…
发表回复
wwfxgm 2020-10-21
- 打赏
- 举报
这写法，太经典了。每天学习一点。
卖水果的net 2020-10-15
- 打赏
- 举报
经典的断号查询需求。
中国风 2020-10-15
- 打赏
- 举报
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
*/中国风 2020-10-15
- 打赏
- 举报
存储数据不规范，效率低
A1000009-->A10000009
A10000010
ダ雨夹雪リ 2020-10-15
- 打赏
- 举报
--没顺序不怕,关键是你的数据整齐不,你确定一下,我是看整齐的那种来做的
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
webjock 2020-10-15
- 打赏
- 举报
有一个缺陷就是没有做val的排序，如果顺序乱掉的话就没办法了
webjock 2020-10-15
- 打赏
- 举报
大神，给跪了
ダ雨夹雪リ 2020-10-15
- 打赏
- 举报
--我晕,你的数据位数不一样
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
ダ雨夹雪リ 2020-10-15
- 打赏
- 举报
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
