自加入排名查询(Self join in rank query)

自加入排名查询(Self join in rank query)

我想在以下查询中执行自加入：

select RANK() OVER (PARTITION BY CARD_ID, BE_BUSINESS_DATE ORDER BY CARD_TXN_SEQ_NUMBER) as Trip_Leg_Id from [dbo].[Src_Nol_Txns] s1 inner join

所以，我加入目前的记录与等级-1的记录如何实现呢？

I want to perform self join on the following query:

select RANK() OVER (PARTITION BY CARD_ID, BE_BUSINESS_DATE ORDER BY CARD_TXN_SEQ_NUMBER) as Trip_Leg_Id from [dbo].[Src_Nol_Txns] s1 inner join

so that, I join the current record with the record of rank -1 how to achieve that?

最满意答案

with cte as ( SELECT * , LAG(CARD_TXN_SEQ_NUMBER, 1,0) OVER (PARTITION BY CARD_ID, BE_BUSINESS_DATE ORDER BY CARD_TXN_SEQ_NUMBER) AS PreviousSEQ FROM table ); select cte1.*, cte2.* from cte as cte1 join cte as cte2 on cte2.CARD_ID = cte1.CARD_ID and cte2.BE_BUSINESS_DATE = cte1.BE_BUSINESS_DATE and cte2.CARD_TXN_SEQ_NUMBER = cte1.PreviousSEQ and cte1.PreviousSEQ <> cte1.CARD_TXN_SEQ_NUMBER;

我认为最后一个将处理重复

with cte as ( SELECT * , LAG(CARD_TXN_SEQ_NUMBER, 1,0) OVER (PARTITION BY CARD_ID, BE_BUSINESS_DATE ORDER BY CARD_TXN_SEQ_NUMBER) AS PreviousSEQ FROM table ); select cte1.*, cte2.* from cte as cte1 join cte as cte2 on cte2.CARD_ID = cte1.CARD_ID and cte2.BE_BUSINESS_DATE = cte1.BE_BUSINESS_DATE and cte2.CARD_TXN_SEQ_NUMBER = cte1.PreviousSEQ and cte1.PreviousSEQ <> cte1.CARD_TXN_SEQ_NUMBER;

I think the last and will deal with duplicates