如果条件满足数字，则更新与上行相同的行

Question

我有这样的数据

id      type
1.      a
2.      b
3.      c
4.      c
5.      d
6.      c
7.      c

目标

id   type.    group
1.      a.          1
2.      b.          2
3.      c.          2
4.      c.          2
5.      d.          3
6.      c.          3
7.      c.          3

如果键入 C，则组值从上面的行中获取值。 我可以通过循环条件和更新来实现这一目标，但这需要很多时间，因为循环更新许多行

如何使用SQL Server 2008的单个更新语句来实现此目的

Answer 1

这应该有效

declare @t table (id int primary key, val char(1));
insert into @t values 
       (1, 'a')
     , (2, 'b')
     , (3, 'c')
     , (4, 'c')
     , (5, 'd')
     , (6, 'c')
     , (7, 'c');
select * 
     , sum(case when val = 'c' then 0 else 1 end) over (order by id) as grp
from @t t 
order by id;
id          val  grp
----------- ---- -----------
1           a    1
2           b    2
3           c    2
4           c    2
5           d    3
6           c    3
7           c    3

点击下面的链接进行演示。

演示

Answer 2

我将其发布为@palarazzi给出的答案的替代方案，这可能已经足够了。 这个答案对于任何数量的输入字母都是稳健的。

WITH cte AS (
    SELECT t1.id AS id1, t1.type AS type1, t2.id AS id2, t2.type AS type2
    FROM yourTable t1
    INNER JOIN yourTable t2
        ON (t1.id = t2.id AND t1.type <> 'c') OR
            (t1.id > t2.id AND t1.type = 'c' AND t2.type <> 'c')
),
cte2 AS (
    SELECT id1, type2,
        ROW_NUMBER() OVER (PARTITION BY id1 ORDER BY id2 DESC) rn
    FROM cte
)
SELECT
    id1 AS id, type2 AS type,
    DENSE_RANK() OVER (ORDER BY type2) [group]
FROM cte2
WHERE rn = 1;

演示