时间戳

首页 > 解决方案 > 基于集合的堆叠更新查询

sql-server - 基于集合的堆叠更新查询

问题描述

我有一个需要设置两个标志值之一的表，Flag1并且Flag2.

   Create Table StackUpdateTable
       (Id Int, GroupId Int, Flag1 Bit, Flag2 Bit, Requirements Int)

现在，这里是规则：

  1. Requirements将是1或2。
  2. Requirements指定在可以为给定顺序（升序）设置值之前必须Flag1设置的前面值的数量。Flag2GroupIdId
  3. Flag2设置后，序列再次开始（对于每个GroupId）

如果有一种方法可以在此表（以及所有新出现的表）上进行基于集合的更新，可以为每组记录设置标志，我一直在努力思考GroupId。

为了进一步说明，请考虑以下数据：

Insert Into StackUpdateTable Values
     (1, 100, 0, 0, 1)
    ,(2, 100, 0, 0, 1)
    ,(3, 101, 0, 0, 1)
    ,(4, 102, 0, 0, 2)
    ,(5, 102, 0, 0, 2)
    ,(6, 102, 0, 0, 2)
    ,(7, 103, 1, 0, 1)
    ,(8, 103, 0, 0, 1)
    ,(9, 103, 0, 0, 1)
    ,(10,104, 1, 0, 2)
    ,(11,105, 1, 0, 2)
    ,(12,106, 0, 0, 2)
    ,(13,106, 0, 0, 2)
    ,(14,106, 0, 0, 2)
    ,(15,106, 0, 0, 2)
;

鉴于此数据，生成的更新数据应如下所示

 1 100 1 0 1 <-- Flag1 Set
 2 100 0 1 1 <-- Flag2 Set
 3 101 1 0 1 <-- Flag1 Set
 4 102 1 0 2 <-- 1st Flag1 Set
 5 102 1 0 2 <-- 2nd Flag1 Set
 6 102 0 1 2 <-- Flag2 Set
 7 103 1 0 1 <-- Unchanged
 8 103 0 1 1 <-- Flag2 Set
 9 103 1 0 1 <-- Flag1 Set
10 104 1 0 2 <-- Unchanged
11 104 1 0 2 <-- Unchanged
12 106 1 0 2 <-- 1st Flag1 Set
13 106 1 0 2 <-- 2nd Flag1 Set
14 106 0 1 2 <-- Flag2 Set
15 106 1 0 2 <-- 1st Flag1 Set

如果添加更多数据，那么...

Insert Into StackUpdateTable Values
     (16,100, 0, 0, 1)
    ,(17,103, 0, 0, 1)
    ,(18,106, 0, 0, 2)
;

...然后更新的数据集（按GroupId\Id顺序）应如下所示：

 1 100 1 0 1 <-- Unchanged
 2 100 0 1 1 <-- Unchanged
16 100 1 0 1 <-- Flag1 Set
 3 101 1 0 1 <-- Unchanged
 4 102 1 0 2 <-- Unchanged
 5 102 1 0 2 <-- Unchanged
 6 102 0 1 2 <-- Unchanged
 7 103 1 0 1 <-- Unchanged
 8 103 0 1 1 <-- Unchanged
 9 103 1 0 1 <-- Unchanged
17 103 0 1 1 <-- Flag2 Set
10 104 1 0 2 <-- Unchanged
11 104 1 0 2 <-- Unchanged
12 106 1 0 2 <-- Unchanged
13 106 1 0 2 <-- Unchanged
14 106 0 1 2 <-- Unchanged
15 106 1 0 2 <-- Unchanged
18 106 1 0 2 <-- Flag1 Set

最后，如果应该插入以下行：

Insert Into StackUpdateTable Values
     (19,106, 0, 0, 2)
;

那么我期望：

 1 100 1 0 1 <-- Unchanged
 2 100 0 1 1 <-- Unchanged
16 100 1 0 1 <-- Flag1 Set
 3 101 1 0 1 <-- Unchanged
 4 102 1 0 2 <-- Unchanged
 5 102 1 0 2 <-- Unchanged
 6 102 0 1 2 <-- Unchanged
 7 103 1 0 1 <-- Unchanged
 8 103 0 1 1 <-- Unchanged
 9 103 1 0 1 <-- Unchanged
17 103 0 1 1 <-- Flag2 Set
10 104 1 0 2 <-- Unchanged
11 104 1 0 2 <-- Unchanged
12 106 1 0 2 <-- Unchanged
13 106 1 0 2 <-- Unchanged
14 106 0 1 2 <-- Unchanged
15 106 1 0 2 <-- Unchanged
18 106 1 0 2 <-- Flag1 Set
19 106 0 1 2 <-- Flag2 Set

我一直在使用 Windowing 函数Row_Number() Over (Partition By GroupId Order By Id) As _seq来组织数据，然后是几个Lag函数Lag(Flag1, 1, 0) Over (Partition By GroupId Order By Id) As _Calc1等，但我在尝试处理新记录时失败了（其中值从零开始）。

我想我也需要开始做记录计数，但我不确定如何处理——我想我也许可以做点什么_seq，但仍然一无所获。

我想了解每次解析此表时如何进行基于集合的更新。它不必是一个单一的更新，事实上，我非常乐意在混合中删除一个 Temp 表，或者一个 CTE 或其他任何东西。我不想做的一件事是我必须通过Id或来游标GroupId。

我已经准备好听到我所要求的只是无法完成，但是我生活在希望中！

标签： sql-server

解决方案

使用来自评论的 ZLK 查询，我将其转换为UPDATE您可以在每次将记录添加到表后运行的语句。

UPDATE s
SET s.Flag1 = CASE WHEN t.RN > 0 THEN 1 ELSE 0 END
    ,s.Flag2 = CASE WHEN t.RN = 0 THEN 1 ELSE 0 END
FROM
(
    SELECT
        *
        ,RN = ROW_NUMBER() OVER ( PARTITION BY GroupID, Requirements ORDER BY ID ) % ( Requirements + 1 )
    FROM StackUpdateTable
) T
INNER JOIN StackUpdateTable s ON s.Id = T.Id

推荐阅读