代码之家 › 专栏 › 技术社区 › Tim Schmelter

我的SQL有什么问题(查找“上一条”记录)

sql sql-server

Tim Schmelter · 技术社区 · 16 年前

我的SQL查询应该返回上一条记录(声明)。 Previous意味着它有一个不同的主键(idData)、一个相等的SSN\u号和一个更早的接收日期。问题是接收日期可能相等,所以我必须寻找另一列。优先级应与排序顺序相同。我做错了什么,因为查询返回了两个ID的另一个记录(两个声明都是彼此的前一个)?

我的两个测试记录如下:
_{(来源:

bilder-hochladen.net

)}

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

3 回复 | 直到 7 年前

Mongus Pong 16 年前

两个记录至少有一个日期比另一个少,所以它们返回彼此的记录。

根据您指定的内容,您可能需要:

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)

看起来很可怕,但这对你来说太可怕了!

Jim Dagg 16 年前

Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date

对于第二个是真的,而你的OR组中的其他三个子句对于第一个是真的。

Tomas Aschan 16 年前

第一排有一个 REPAIR_COMPLETION_DATE 它早于第二行,这意味着它将满足连接条件 Claim.Repair_Completion_Date > PrevClaim.Repair_Completion_Date ,从而符合“先前”权利要求的条件。