我的SQL查询应该返回以前的记录(声明)。Previous表示它具有不同的主键(idData),相等的SSN_Number和更早的Received_Date。问题是Received_Date可能相等,所以我必须寻找另一列。优先级应与“排序顺序”相同。我在做什么错,因为查询返回的是两个ID的另一个Record(两个声明都是彼此相同的)?
我的两个测试记录如下:( 来源: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
问题是,如果这些日期中的任何一个小于另一个日期,则您将返回上一个记录。
两条记录的日期至少比另一条少,因此它们返回彼此的记录。
根据您指定的内容,您可能需要类似以下内容:
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!