mysql - 不在特定日期范围内的两个相关表之间的 SQL SELECT 数据

Question

我有 2 个多对多表，还有一个用于连接它们的表。

官员

• 编号
• 姓名

举报

• 编号
• 表现日期
• 标题

report_officer

• officer_id
• report_id

我想选择所有从未与报告相关联或在特定时间范围内未与报告相关联的官员。

到目前为止，我已经尝试了以下方法(以下方法对我不起作用!):

SELECT * 
  FROM Officer 
       LEFT JOIN report_officer 
            ON Officer.id = report_officer.officer_id 
       LEFT JOIN Report 
            ON Report.id = report_officer.report_id
 WHERE (performanceDate IS NULL 
        OR performanceDate < "2014-03-23 00:00:00" 
        OR performanceDate > "2014-04-01 00:00:00"
        )

我的左连接查询仅当官员仅在特定时间范围内与报告相关联时才有效，但一旦他们有多个报告就会失败。

结果:

+------------+-----------------+
| officer_id | performanceDate |
+------------+-----------------+
|        130 | NULL            | # good

|        134 | 2014-03-02      | # bad - officer_id 134 has a performanceDate  
|        134 | 2014-03-09      | # on 2014-3-30, I do not want this in the results.
|        134 | 2014-03-16      | # 

|        135 | 2014-03-02      | # good
+------------+-----------------+

SQL fiddle : http://sqlfiddle.com/#!2/1bf72/3 <- 在 sql fiddle 中，请引用我希望返回的列的“名称”字段。

关于如何使这项工作有任何想法吗？

理想情况下，我希望尽可能简单地使用我的 ORM。我正在使用 doctrine，并且不希望开始使用完全自定义的代码(因此，如果仅通过连接就可以完成，那就太好了)。我虽然有一种不好的预感，但我需要一个子查询。

Answer 1

SELECT Officer.*, Report.performanceDate FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id 
LEFT JOIN Report ON Report.id = report_officer.report_id
 AND
   (performanceDate > "2014-03-23 00:00:00" AND
    performanceDate < "2014-04-01 00:00:00")
WHERE Report.id IS NULL

您只想联接特定日期范围内的行，因此您必须将约束移动到联接的 on 子句中并反转约束。

如果你想删除重复项，你可以尝试group by:

SELECT Officer.id, MAX(Report.performanceDate) FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id 
LEFT JOIN Report ON Report.id = report_officer.report_id
 AND
   (performanceDate > "2014-03-23 00:00:00" AND
    performanceDate < "2014-04-01 00:00:00")
WHERE Report.id IS NULL
GROUP BY Officer.id

但如果在您请求的日期范围内有多个演出日期(或者您可以使用 GROUP_CONCAT 来收集所有日期)，您必须决定要获取哪个日期。

更新

实际上我比较确定，LEFT JOIN 根本不可能实现您想要实现的目标...

总是有效的是子查询解决方案:

SELECT Officer.id as OfficerID, Officer.name,
Report.id as ReportID,
Report.performanceDate

FROM Officer
LEFT JOIN report_officer
  ON Officer.id = report_officer.officer_id 
LEFT JOIN Report
  ON Report.id = report_officer.report_id

WHERE Report.id IS NULL 
OR NOT EXISTS (
    SELECT * FROM report_officer
    INNER JOIN Report ON report_id = Report.id
    WHERE officer_id = Officer.id AND
      performanceDate > "2014-03-23 00:00:00" 
      AND performanceDate < "2014-04-01 00:00:00"
)

但是这些都不是那么高效......这个看起来是否有应该禁止输出行的报告。