大数据分析上下数据有关联(根据排序)

Question

ID               TASK             COLOR
1                  001              R003
2                  002              R002
3                  003              R002
4                  004              R003
5                  005              R003
6                  006              R002按照ID排序来识别 哪些数据上下的color是一样的,把连续color的TASK显示出来,记住一定要连续的,跳开ID不算上面的数据查询得到
R002(002-003)
R003(004-005)

Answer 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)>1drop table #A

Answer 2

看看这样行不
--测试数据
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