34,594
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[TASK] nvarchar(23),[COLOR] nvarchar(24))
Insert #T
select 1,N'001',N'R003' union all
select 2,N'002',N'R002' union all
select 3,N'003',N'R002' union all
select 4,N'004',N'R003' union all
select 5,N'005',N'R003' union all
select 6,N'006',N'R002'
Go
--测试数据结束
;WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY
COLOR
ORDER BY
ID
) - ID AS tempid
FROM
#T
)
SELECT a.COLOR ,
STUFF(( SELECT '-' + cte.TASK
FROM cte
WHERE cte.tempid = a.tempid
FOR
XML PATH('')
), 1, 1, '') AS value
FROM cte a
GROUP BY a.COLOR ,a.tempid
HAVING COUNT(1)>1
create table #A
(
ID int,
TASK varchar(10),
COLOR varchar(10)
)
insert into #A values(1, '001', 'R003')
insert into #A values(2, '002', 'R002')
insert into #A values(3, '003', 'R002')
insert into #A values(4, '004', 'R003')
insert into #A values(5, '005', 'R003')
insert into #A values(6, '006', 'R002')
select max(color)+'('+min(task)+'-'+max(task)+')' from
(
select *,row_number() over(order by id) -row_number() over(partition by COLOR order by id) as Y
from #A
) A
group by y
having count(1)>1
drop table #A