sql - 使用 SQL 查询根据连续值更新最小值

Question

尝试更新最小值 CardSerial进专栏MinimumCardSerial .下面是我的表结构。

CardID      CardSerial       Denomination Minimumcardserial
----------- ---------------  ------------ -----------------
2685680     214026002        10           
2685681     214026003        10           
2685682     214026004        10           
2685778     214026100        10           
2685779     214026101        10           
2685780     214026102        10           
2685878     214026200        10           
2685879     214026201        10           
2685880     214026202        10           

预期低于结果

CardID      CardSerial       Minimumcardserial
----------- ---------------- ----------- ------------ -------------
2685680     214026002        214026002
2685681     214026003        214026002
2685682     214026004        214026002
2685778     214026100        214026100
2685779     214026101        214026100
2685780     214026102        214026100
2685878     214026200        214026200
2685879     214026201        214026200
2685880     214026202        214026200

以下查询可用于根据连续组查找最小卡片序列，但尝试查找更新查询以将最小卡片序列更新到源表本身。有人可以帮忙吗？

WITH
  sequenced AS 
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY Denomination ORDER BY cardSerial) AS sequence_id,
    *   FROM  Cards )

SELECT
  min(cardSerial),  Denomination
FROM   Sequenced
GROUP BY
  Denomination, 
  cardSerial - sequence_id
ORDER BY
  Denomination, 
  cardSerial - sequence_id

Answer 1

这是一种间隙和孤岛问题。假设 cardserial是一个数字，你可以减去一个枚举值——对于递增的值，这种差异是恒定的。
然后在该分组中取最小值:

select c.*,
       min(cardserial) over (partition by cardserial - seqnum) as minimum_cardserial
from (select c.*,
             row_number() over (order by cardserial) as seqnum
      from cards c
     ) c;

如 cardserial不是数字，您可以转换为数字:

min(cardserial) over (partition by convert(numeric(38), cardserial)) - seqnum)