sql - 如何通过特定顺序检测值的变化

Question

给定

Id | RouteId | Milepoint | RoadCondition -: | :------ | --------: | :------------  1 | A       |         0 | X              2 | A       |         1 | X              3 | A       |         4 | Y              4 | B       |         0 | Y              5 | B       |         2 | null  6 | B       |         5 | null  7 | B       |         6 | Z              8 | B       |        18 | Z              9 | C       |         0 | X             

我在后面

Id | RouteId | Milepoint | RoadCondition | ContinuousId-: | :------ | --------: | :------------ | -----------: 1 | A       |         0 | X             |            1 2 | A       |         1 | X             |            1 3 | A       |         4 | Y             |            2 4 | B       |         0 | Y             |            3 5 | B       |         2 | null          |            4 6 | B       |         5 | null          |            5 7 | B       |         6 | Z             |            6 8 | B       |        18 | Z             |            6 9 | C       |         0 | X             |            7

DECLARE @Example TABLE (
  Id INT primary key,
  RouteId varchar(50),
  Milepoint INT,
  RoadCondition varchar(50),
  ContinuousId INT
)

db<> fiddle here

我正在使用 SQL Server 2017。

我所追求的是生成 ContinuousId 的能力，它沿着 RouteId ASC、Milepoint ASC 行进并且当 RouteId 或 RoadCondition 变化，它增加 ContinuousId

我知道我想使用 LEAD() 但处理 NULL 是我不确定如何解决问题的部分，因为我不能只合并 NULL，因为没有两个可以假定空值是相同的值，只要它看到 RoadCondition 为 NULL，它就应该递增

Answer 1

您可以将 DENSE_RANK() 与 COALESCE() 一起使用:

select e.*,
       dense_rank() over (order by RouteId, coalesce(RoadCondition, cast(id as varchar(255)))) AS New_ContinuousId
from @Example e;