sql-server - 用于创建团队的 SQL 查询

Question

我需要一个查询来将团队分配给一系列用户。数据如下所示:

UserId  Category    Team
1       A           null
2       A           null
3       B           null
4       B           null
5       A           null
6       B           null
8       A           null
9       B           null
11      B           null

团队应按用户 ID 排序创建，第一个用户 ID 成为团队编号，连续的 A 和后面的 B 一样属于该团队。 B 之后的第一个 A 开始一个新团队。总会有至少一个 A 和一个 B。所以在更新之后，数据应该是这样的:

UserId  Category    Team
1       A           1
2       A           1
3       B           1
4       B           1
5       A           5
6       B           5
8       A           8
9       B           8
11      B           8

编辑:需要补充一点，用户 ID 不会总是递增 1。我编辑了示例数据以说明我的意思。此外，团队 ID 不一定非得是第一个用户的 ID，只要他们最终正确分组即可。例如，用户 1 - 4 可能都在团队“1”中，用户 5 和 6 在团队“2”中，用户 8,9 和 11 在团队“3”中

Answer 1

首先，您可以用递增的数字标记每一行。然后您可以使用 left join 来查找以前的用户。如果前一个用户的类别为 'B'，而当前的类别为 'A'，则意味着新团队的开始。团队编号是在当前 UserId 之前开始新团队的最后一个 UserId。

使用 SQL Server 2008 语法:

; with  numbered as
        (
        select  row_number() over (order by UserId) rn
        ,       *
        from    Table1
        )
,       changes as
        (
        select  cur.UserId
        ,       case
                when prev.Category = 'B' and cur.Category = 'A' then cur.UserId
                when prev.Category is null then cur.UserId
                end as Team
        from    numbered cur
        left join
                numbered prev
        on      cur.rn = prev.rn + 1
        )
update  t1
set     Team = team.Team
from    Table1 t1
outer apply
        (
        select  top 1 c.Team
        from    changes c
        where   c.UserId <= t1.UserId
                and c.Team is not null
        order by
                c.UserId desc
        ) as team;

Example at SQL Fiddle.