一尘不染

我的SQL有什么问题?(找到「上一个」记录)

sql

我的SQL查询应该返回以前的记录(声明)。Previous表示它具有不同的主键(idData),相等的SSN_Number和更早的Received_Date。问题是Received_Date可能相等,所以我必须寻找另一列。优先级应与“排序顺序”相同。我在做什么错,因为查询返回的是两个ID的另一个Record(两个声明都是彼此相同的)?

我的两个测试记录如下:( 来源:bilder-hochladen.net相同的SSN_Number

SQL查询是这样的:

SELECT TOP (1) Claim.idData     AS ClaimID, 
               PrevClaim.idData AS PrevClaimID 
FROM   tabData AS Claim 
       INNER JOIN tabData AS PrevClaim 
         ON Claim.SSN_Number = PrevClaim.SSN_Number 
            AND Claim.idData <> PrevClaim.idData 
            AND ( Claim.Received_Date > PrevClaim.Received_Date 
                   OR Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date 
                   OR Claim.Repair_Completion_Date > 
                      PrevClaim.Repair_Completion_Date 
                   OR Claim.Claim_Submitted_Date > 
                      PrevClaim.Claim_Submitted_Date ) 
WHERE  ( Claim.idData = @claimID ) 
ORDER  BY PrevClaim.Received_Date DESC, 
          PrevClaim.Claim_Creation_Date DESC, 
          PrevClaim.Repair_Completion_Date DESC, 
          PrevClaim.Claim_Submitted_Date DESC

编辑 :根据Mongus Pong的回答,这是正确的sql:

SELECT  TOP (1) Claim.idData AS ClaimID, PrevClaim.idData AS PrevClaimID
FROM    tabData AS Claim INNER JOIN tabData AS PrevClaim 
        ON Claim.SSN_Number = PrevClaim.SSN_Number 
        AND Claim.idData <> PrevClaim.idData 
        AND ( Claim.Received_Date > PrevClaim.Received_Date 
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date 
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date
         OR Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date = PrevClaim.Repair_Completion_Date AND Claim.Claim_Submitted_Date > PrevClaim.Claim_Submitted_Date )
WHERE   ( Claim.idData = @claimID )
ORDER BY PrevClaim.Received_Date          DESC, 
         PrevClaim.Claim_Creation_Date    DESC, 
         PrevClaim.Repair_Completion_Date DESC,
         PrevClaim.Claim_Submitted_Date   DESC

阅读 108

收藏
2021-05-23

共1个答案

一尘不染

问题是,如果这些日期中的任何一个小于另一个日期,则您将返回上一个记录。

两条记录的日期至少比另一条少,因此它们返回彼此的记录。

根据您指定的内容,您可能需要类似以下内容:

Claim.Received_Date > PrevClaim.Received_Date OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date > PrevClaim.Claim_Creation_Date) OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date) OR
(Claim.Received_Date = PrevClaim.Received_Date AND Claim.Claim_Creation_Date = PrevClaim.Claim_Creation_Date AND Claim.Repair_Completion_Date = PrevClaim.Repair_Completion_Date AND Claim.Claim_Submitted_Date > PrevClaim.Claim_Submitted_Date)

看起来很可怕,但是那对您来说就是SQL!

2021-05-23