sql - 查询显示表之间不匹配的记录

Question

我有两张 table 。其中一张是已提交给我们的报告表。另一个是临时表，其中包含最终应提交给我们的报告记录。我想仅显示临时表中与报告表中的记录不匹配的记录(因此仍显示必须提交的报告)。

示例数据是:

Reports table:

CREATE TABLE [dbo].[Reports]  
(  
    [ReportID] [int] IDENTITY(1,1) NOT NULL,  
    [ReportDate] [date] NULL,  
    [AssessmentID] [int] NOT NULL,  
    [ReportType] [varchar](50) NULL  
);  

AssessmentID    ReportType  ReportID  
1   1st Quarterly   27  
2   1st Quarterly   30  
2   2nd Quarterly   31  
2   3rd Quarterly   32  

QuarterlyReportsDue table:

CREATE TABLE #QuarterlyReportsDue  
(  
AssessmentID INT,  
InstallationDate DATE,  
QuarterlyReportType VARCHAR(50)  
);  

AssessmentID    InstallationDate    QuarterlyReportType  
1   2009-08-14  1st Quarterly  
1   2009-08-14  2nd Quarterly  
1   2009-08-14  3rd Quarterly  
1   2009-08-14  4th Quarterly  
2   2008-05-16  4th Quarterly  
2   2008-05-16  3rd Quarterly  
2   2008-05-16  2nd Quarterly  
2   2008-05-16  1st Quarterly  

我尝试过 LEFT OUTER JOINS 但遇到了问题。请看我下面的SQL:

SELECT #QuarterlyReportsDue.InstallationDate, #QuarterlyReportsDue.QuarterlyReportType, Reports.ReportType  
FROM #QuarterlyReportsDue  
LEFT OUTER JOIN Reports ON #QuarterlyReportsDue.AssessmentID = Reports.AssessmentID  
WHERE Reports.ReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly')  
AND Reports.ReportType <> #QuarterlyReportsDue.QuarterlyReportType  
ORDER BY #QuarterlyReportsDue.AssessmentID  

我的结果:

AssessmentID    QuarterlyReportType ReportType  ReportID  
1   2nd Quarterly   1st Quarterly   27  
1   3rd Quarterly   1st Quarterly   27  
1   4th Quarterly   1st Quarterly   27  
2   4th Quarterly   1st Quarterly   30  
2   4th Quarterly   2nd Quarterly   31  
2   4th Quarterly   3rd Quarterly   32  
2   1st Quarterly   2nd Quarterly   31  
2   1st Quarterly   3rd Quarterly   32  
2   3rd Quarterly   1st Quarterly   30  
2   3rd Quarterly   2nd Quarterly   31  
2   2nd Quarterly   1st Quarterly   30  
2   2nd Quarterly   3rd Quarterly   32  

对于评估 1，效果很好，评估 2 有很多重复项。我怎样才能解决这个问题，只显示理想的结果？

AssessmentID    QuarterlyReportType ReportType  
1   2nd Quarterly   1st Quarterly  
1   3rd Quarterly   1st Quarterly  
1   4th Quarterly   1st Quarterly  
2       4th Quarterly     

Answer 1

当您 LEFT JOIN 到一个表，然后在 WHERE 子句中引用该表的列之一时，您会隐式地将连接转换为 INNER JOIN。相反，请将这些条件移出 WHERE 并使它们成为 JOIN 条件的一部分。

SELECT q.InstallationDate, q.QuarterlyReportType, Reports.ReportType  
    FROM #QuarterlyReportsDue q
        LEFT OUTER JOIN Reports r
            ON q.AssessmentID = r.AssessmentID
                AND q.QuarterlyReportType = r.ReportType  
                AND r.ReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly')  
    WHERE r.AssessmentID IS NULL /* matching record not found in Reports table */
    ORDER BY #QuarterlyReportsDue.AssessmentID