34,575
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([序号] int,[姓名] nvarchar(21))
Insert #T
select 1,N'王' union all
select 2,N'王' union all
select 3,N'王' union all
select 4,N'张' union all
select 5,N'王' union all
select 6,N'王' union all
select 7,N'张' union all
select 8,N'王' union all
select 9,N'王'
Go
--测试数据结束
;WITH cte AS (
Select *,序号-ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY 序号) AS rn from #T
),temp AS (
SELECT MAX(序号) AS max序号,MIN(序号) AS min序号,姓名,rn FROM cte GROUP BY 姓名,rn
)
SELECT STUFF(( SELECT ',' + ( CASE WHEN max序号 > min序号
THEN RTRIM(min序号) + '-' + RTRIM(MAX序号)
ELSE RTRIM(min序号)
END )
FROM temp b
WHERE a.姓名 = b.姓名
FOR
XML PATH('')
), 1, 1, '') AS 序号 ,
a.姓名
FROM temp a
GROUP BY a.姓名
;WITH CTET
AS
(
SELECT CASE WHEN COUNT(*) = 1 THEN RTRIM(MIN(序号))
ELSE RTRIM(MIN(序号)) + '-' + RTRIM(MAX(序号))
END AS 序号 ,
姓名 ,
grp
FROM ( SELECT * ,
序号 - ROW_NUMBER() OVER ( PARTITION BY 姓名 ORDER BY 序号 ) AS grp
FROM Tab1
) AS t
GROUP BY grp ,
姓名
)
SELECT STUFF((SELECT ','+序号 FROM CTET WHERE 姓名=T.姓名 FOR XML PATH('')),1,1,'') AS 序号,姓名 FROM CTET AS T GROUP BY 姓名
if object_id('tempdb..#Tmp_Data') is not null
drop table #Tmp_Data
CREATE TABLE #Tmp_Data (
Seq_No varchar(10) ,
Name nvarchar(20))
Insert into #Tmp_Data
Select 1,'王' union
Select 2,'王' union
Select 3,'王' union
Select 4,'张' union
Select 5,'王' union
Select 6,'王' union
Select 7,'张' union
Select 8,'王' union
Select 9,'王'
if object_id('tempdb..#Tmp_Data1') is not null
drop table #Tmp_Data1
CREATE TABLE #Tmp_Data1 (
List_ID int identity(1,1),
Seq_No varchar(10) ,
Name nvarchar(20),
Group_No int)
Insert into #Tmp_Data1 (Seq_No,Name)
Select Seq_No,Name From #Tmp_Data Group By Name,Seq_No Order By Name,Seq_No
UPDATE #Tmp_Data1 SET Group_No=Seq_No-List_ID
--Select * From #Tmp_Data1
if object_id('tempdb..#Tmp_Data2') is not null
drop table #Tmp_Data2
CREATE TABLE #Tmp_Data2 (
List_ID int identity(1,1),
Seq_No varchar(10) ,
Name nvarchar(20),
Group_No int)
Insert into #Tmp_Data2
Select Seq_No=case when count(*)=1
then cast(Min(Seq_No) as varchar)
else cast(Min(Seq_No) as varchar)+'-'+cast(MAX(Seq_No) as varchar) end,Name,Group_No From #Tmp_Data1 Group By Name,Group_No
--Select * From #Tmp_Data2
SELECT Name,
Seq_No=CAST(MIN(Seq_No) as varchar)
+CASE
WHEN COUNT(*)=3 THEN ','
+CAST((SELECT Seq_No FROM #Tmp_Data2 WHERE Name=a.Name AND Seq_No NOT IN(MAX(a.Seq_No),MIN(a.Seq_No))) as varchar)
ELSE ''
END
+CASE
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(Seq_No) as varchar)
ELSE ''
END
FROM #Tmp_Data2 a
GROUP BY Name