sql - 每当业务发生变化时标记

Question

数据#TRAN

ID                          Business              STDT      
501                         50005038             20170208             
501                         50005038             20170801
501                         50005035             20170802
501                         50005038             20171101
501                         50005038             20180101
501                         50005035             20180201
514                         50005038             20170208
514                         50005038             20170423
514                         50005038             20170801
514                         50005038             20170801
514                         50005035             20170802
514                         50005038             20171101
514                         50005035             20171201

我正在尝试标记那些业务代码正在更改的事件。我尝试了下面提到的查询，但是我得到的结果也显示了以前的业务代码。我想要只标记更改的业务代码行的查询

我试过这个查询

代码：

with cte as 
(
select *, ROW_NUMBER() over(partition by ID order by STDT) as rn
from #TRAN
)

,cte1 as
(
select c1.ID, c1.Business as S1, c1.STDT as T1, c2.Business as S2, c2.STDT as T2, c1.[], c1.
from cte c1 join cte c2
       on c1.rn + 1 = c2.rn
          and c1.ID = c2.ID
where c1.Business <> c2.Business
)

select ID,[], ,
       case n 
              when 1 then S1
              when 2 then S2
       end as Business_Code,
       case n 
              when 1 then T1
              when 2 then T2
       end as Timestamp
from cte1 cross join (select 1 n union all select 2) nums
order by ID, [Timestamp];

结果我得到-->

ID            Business              Timestamp
501         50005038             20170801
501         50005035             20170802
501         50005035             20170802
501         50005038             20171101
501         50005038             20180101
501         50005035             20180201
514         50005038             20170801
514         50005035             20170802
514         50005035             20170802
514         50005038             20171101
514         50005038             20171101

结果我想要-->

ID                          Business              STDT                      Flag
501                         50005038             20170208             
501                         50005038             20170801
501                         50005035             20170802             1
501                         50005038             20171101             1
501                         50005038             20180101
501                         50005035             20180201             1
514                         50005038             20170208
514                         50005038             20170423
514                         50005038             20170801
514                         50005038             20170801
514                         50005035             20170802             1
514                         50005038             20171101             1
514                         50005035             20171201             1

标签： sql

Answer 1

GMB 的回答很好，但可以简化为：

select t.*,
       (case when business = lag(business) over (partition by id order by stdt)
             then null else 1
        end) as flag
from mytable t;