none
求教按顺序标识各组的SQL语句 RRS feed

  • 问题

  • 假设有测试表如下,a列中1或0出现的行数不固定,grp列已初始化为0:

    if object_id('tb') is not null
    drop table tb
    go
    create table tb(a int,grp int)
    go
    insert into tb
    select 1,0 union all
    select 1,0 union all
    select 1,0 union all
    select 0,0 union all
    select 0,0 union all
    select 0,0 union all
    select 0,0 union all
    select 1,0 union all
    select 1,0 union all
    select 1,0 union all
    select 1,0 union all
    select 0,0 union all
    select 0,0 union all
    select 1,0 union all
    select 1,0
    go

    求SQL语句得到如下结果:

    a grp
    1 1
    1 1
    1 1
    0 0
    0 0
    0 0
    0 0
    1 2
    1 2
    1 2
    1 2
    0 0
    0 0
    1 3
    1 3

    2017年8月1日 9:22

答案

  • Hi zy662,

    您好!你可以通过使用 LAG, SUM 窗体函数,实现上述结果,示例代码如下:

    if object_id('tb') is not null
    drop table tb
    go
    create table tb(id int,a int,grp int)
    go
    insert into tb
    select 1,1,0 union all
    select 2,1,0 union all
    select 3,1,0 union all
    select 4,0,0 union all
    select 5,0,0 union all
    select 6,0,0 union all
    select 8,0,0 union all
    select 11,1,0 union all
    select 12,1,0 union all
    select 15,1,0 union all
    select 16,1,0 union all
    select 17,0,0 union all
    select 20,0,0 union all
    select 22,1,0 union all
    select 26,1,0
    go
    
    with cte as
    (
    select 
    case when LAG(a,1,2) over (order by id)=2 or (LAG(a,1,2) over (order by id)=0 and a=1) then a else 0 end as a_1,
    a,
    id
    from tb
    )
    select 
    id,
    a,
    case when a=1 then sum(a_1) over (order by id) else 0 end as grep
    
    from cte

    Best Regards,

    Will



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 zy662 2017年8月3日 5:45
    2017年8月2日 11:01

全部回复

  • declare @grp int, @a int
    set @grp = 0;
    update tb set
     @grp = case when a=0 or @a=a then @grp else @grp + 1 end,
     @a = a,
     grp = case when a=0 then 0 else @grp end
    ;
    select * from tb;
    2017年8月1日 9:45
  • 你这个需求是根据存储顺序的,但实际上 sql 操作不确保处理的数据匹配存储数据,所以这个结果其实无法得到保障 
    2017年8月1日 9:46
  • UPDATE t3
    SET grp=GrpNr
    FROM 
    (SELECT *,DENSE_RANK()OVER(PARTITION BY a ORDER BY Grp2) AS GrpNr FROM (SELECT *,RN-ROW_NUMBER()OVER(PARTITION BY a ORDER BY RN) AS Grp2 FROM (SELECT *,ROW_NUMBER()OVER(ORDER BY RAND()) AS RN FROM tb) AS t) AS t2 WHERE t2.a>=1) AS t3
    
    SELECT * FROM tb
    /*
    1	1
    1	1
    1	1
    0	0
    0	0
    0	0
    0	0
    1	2
    1	2
    1	2
    1	2
    0	0
    0	0
    1	3
    1	3
    */


    Roy Wu(吳熹Blog)(微博)

    2017年8月2日 3:19
    版主
  • 很巧妙的写法,感谢回复。
    2017年8月2日 6:29
  • 是的,假如原始表中还有一个标识列id,能保证在id有顺序的前提下,得到想要的结果吗?

    比如表变成如下的形式:

    if object_id('tb') is not null
    drop table tb
    go
    create table tb(id int,a int,grp int)
    go
    insert into tb
    select 1,1,0 union all
    select 2,1,0 union all
    select 3,1,0 union all
    select 4,0,0 union all
    select 5,0,0 union all
    select 6,0,0 union all
    select 8,0,0 union all
    select 11,1,0 union all
    select 12,1,0 union all
    select 15,1,0 union all
    select 16,1,0 union all
    select 17,0,0 union all
    select 20,0,0 union all
    select 22,1,0 union all
    select 26,1,0
    go

    得到:

    id a grp
    1 1 1
    2 1 1
    3 1 1
    4 0 0
    5 0 0
    6 0 0
    8 0 0
    11 1 2
    12 1 2
    15 1 2
    16 1 2
    17 0 0
    20 0 0
    22 1 3
    26 1 3

    2017年8月2日 6:35
  • 谢谢哦。这种写法可以得到预想的结果,但是用RAND()函数排序,能保证得到跟原始表一样的顺序吗?
    • 已编辑 zy662 2017年8月2日 6:45
    2017年8月2日 6:45
  • RAND--可以是任何常量都行没意义,只要是一个固定的值就行,这是取表默认顺序
    如#1
    zjcxc.邹建写法很危险,会受到表有索引的影响,比如:覆盖索引直接会用到这索引非物理顺序

    Roy Wu(吳熹Blog)(微博)

    2017年8月2日 7:43
    版主
  • 不是用了ORDER BY RAND()吗?为什么还是取表的默认顺序呢?
    2017年8月2日 10:18
  • Hi zy662,

    您好!你可以通过使用 LAG, SUM 窗体函数,实现上述结果,示例代码如下:

    if object_id('tb') is not null
    drop table tb
    go
    create table tb(id int,a int,grp int)
    go
    insert into tb
    select 1,1,0 union all
    select 2,1,0 union all
    select 3,1,0 union all
    select 4,0,0 union all
    select 5,0,0 union all
    select 6,0,0 union all
    select 8,0,0 union all
    select 11,1,0 union all
    select 12,1,0 union all
    select 15,1,0 union all
    select 16,1,0 union all
    select 17,0,0 union all
    select 20,0,0 union all
    select 22,1,0 union all
    select 26,1,0
    go
    
    with cte as
    (
    select 
    case when LAG(a,1,2) over (order by id)=2 or (LAG(a,1,2) over (order by id)=0 and a=1) then a else 0 end as a_1,
    a,
    id
    from tb
    )
    select 
    id,
    a,
    case when a=1 then sum(a_1) over (order by id) else 0 end as grep
    
    from cte

    Best Regards,

    Will



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 zy662 2017年8月3日 5:45
    2017年8月2日 11:01
  • 不是用了ORDER BY RAND()吗?为什么还是取表的默认顺序呢?
    看执行计划
    不明白时要学会多动手调试,自己才能加深印象学到知识

    Roy Wu(吳熹Blog)(微博)

    2017年8月3日 1:40
    版主
  • 按照 id 顺序的话,直接把 3 楼 order by rand() 改在 order by id 就行了
    2017年8月3日 2:59
  • 感谢,这种写法的思路比较清楚。
    2017年8月3日 5:46
  • 好的,谢谢。
    2017年8月3日 5:54
  • 从查询计划上来看,是按a列、表达式1004升序。表达式1004能说明是按表的默认顺序排列的吗?


    • 已编辑 zy662 2017年8月4日 14:39
    2017年8月4日 14:05