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

问题描述：

我有两个表。一个是提交给我们的报告表格。另一个是临时表格，其中包含最终应提交给我们的报告记录。我想只显示临时表中与记录表中的记录不匹配的记录（因此仍显示必须提交的报告）。查询显示表之间不匹配的记录

实例数据是：

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

我已经试过左外连接，但我遇到了问题。请参阅我下面的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

答

当你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

也很棒！感谢您的解释。 – 2010-11-30 20:47:49

答

您应该使用NOT EXISTS来查找临时表中没有提交的报表中的条目的条目。

+1提NOT EXISTS。 – Aliostad 2010-11-30 20:17:15

答

这个连接会将两个表中的记录相互叠加（笛卡尔），这就是为什么你会获得更多记录回来。

请记住，您正在加入AssessmentId并且有多个记录与assessmentId相同。虽然您正在过滤掉那些不具有相同ReportType但您碰到新组合。

SELECT #QuarterlyReportsDue.InstallationDate, 
     #QuarterlyReportsDue.QuarterlyReportType 
FROM #QuarterlyReportsDue 
WHERE NOT EXISTS 
     (
     SELECT 1 
     FROM Reports 
     WHERE Reports.ReportType = QuarterlyReportsDue.QuarterlyReportType 
     AND #QuarterlyReportsDue.AssessmentID = Reports.AssessmentID 
    )

答

也许这样的事情？

SELECT  qrd.InstallationDate, 
      qrd.QuarterlyReportType 
FROM  #QuarterlyReportsDue qrd 
WHERE  qrd.QuarterlyReportType IN ('1st Quarterly', '2nd Quarterly', '3rd Quarterly', '4th Quarterly') 
      AND NOT EXISTS (
       SELECT 1 
       FROM  Reports r 
       WHERE r.AssessmentID = qrd.AssessmentID 
          AND r.ReportType = qrd.QuarterlyReportType 
      ) 
ORDER BY qrd.AssessmentID

完美的作品。谢谢！ – 2010-11-30 20:45:29

答

select * 
from Reports r 
left join #QuarterlyReportsDue d 
    on d.AssessmentID=r.AssessmentID 
    and d.QuarterlyReportType=ReportType 
where d.AssessmentID is null

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

相关推荐