自加入排名查询(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 joinso 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
更多推荐
发布评论