SQL查询-值不能与上一行相同

Question

我试图根据项目号和事务类型返回一些数据。我只想看到每种事务类型的最新日期。如何使给定项目号的rn 1中的事务类型与给定项目号的rn 2中的事务类型不相等。

SELECT 
    *
FROM 
    (SELECT  
         [ItemN], 
         [TransacDate], 
         [TransacType],
         ROW_NUMBER() OVER(PARTITION BY [ItemN] ORDER BY [ItemN] ASC) rn
     FROM 
         [Huxley].[dbo].[FGInvDetail]
     WHERE
         ([TransacType] = 1 OR [TransacType] = 3)) a
WHERE 
    rn = 1 OR rn = 2

查询结果

​

​

Answer 1

尝试将transactype添加到分区并通过transactype进行排序。最后，您只希望保持rn =1。

   SELECT * FROM (
                SELECT  [ItemN], 
                        [TransacDate], 
                        [TransacType],
                        ROW_NUMBER() OVER(PARTITION BY [ItemN], [transactype] ORDER BY [transacdate] Desc) rn
                    FROM [Huxley].[dbo].[FGInvDetail]
                    where ([TransacType] = 1 or [TransacType] = 3)
              ) a
WHERE rn = 1