求一个sql

wzllin111 2017-05-26 09:42:22


按姓名把序号汇总起来,
序号是连续的用区间表示
王 1到3,5到6,8到9
...全文
276 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhouyuehai1978 2017-06-02
  • 打赏
  • 举报
回复
SELECT aaa.[姓名], CASE WHEN MIN(序号) = MAX(序号) THEN CAST(MIN(序号) AS VARCHAR) ELSE CAST(MIN(序号) AS VARCHAR) + '-' + CAST(MAX(序号) AS VARCHAR) END AS 序号 FROM ( SELECT *, 序号 -ROW_NUMBER() OVER(PARTITION BY 姓名 ORDER BY 序号) AS num FROM #T AS t ) AS aaa GROUP BY aaa.num, aaa.[姓名]
RICHEER COCA 2017-05-30
  • 打赏
  • 举报
回复
路过,学习了。
引用 2 楼 sinat_28984567 的回复:
--测试数据
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.姓名
二月十六 版主 2017-05-26
  • 打赏
  • 举报
回复
--测试数据
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.姓名


中国风 2017-05-26
  • 打赏
  • 举报
回复
e.g.
;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 姓名
顺势而为1 2017-05-26
  • 打赏
  • 举报
回复


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


34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧