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

Question

我有2个多对多表，还有一个表来连接它们。

官员

• id
• 名称

报告

• id
• 性能日期
• 所有权

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<-在sqlfiddle中，请参阅我要返回的列的"name"字段。

有什么想法可以让它发挥作用吗？

理想情况下，我希望尽可能简单地使用我的ORM。我使用的是条令，不希望开始使用完全自定义的代码(所以如果只使用联接，那就太好了)。尽管我有一种不好的感觉，我需要一个子查询。

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"
)

但这些并不是表演性的。。。这一个检查是否存在禁止输出该行的报告。

Answer 2

我想选择所有从未与报告或在某个时间表。

您的两个条件是多余的：如果一名军官没有关联，那么他也不能在任何时间段内关联，并且将被第二个条件选中。如果他在时间范围内有报告，那么他不会因为第二种情况而被选中，但他也至少有一份报告，无法满足第一份报告。

所以你想要的是"一个在时间范围内没有报告的官员"。

要做到这一点，只需颠倒条件：首先在所选时间框架内获得报告(即不想要的官员)；则LEFT JOIN与Officer，要求联接产生null。这将让你得到其他官员，那些在所选时间范围内没有报告的官员(或者可能根本没有报告)。

然而，在这种情况下，您不能有报告日期，因为您没有报告(这对于那些根本没有报告的官员来说更为明显)：

SELECT
Officer.id as OfficerID,
Officer.name,
MAX(Report.id) as ReportID,
MAX(performanceDate) AS 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 BETWEEN 20140323 AND 20140401)
GROUP BY Officer.id, Officer.name
HAVING ReportID IS NULL;

我不知道条令和HAVING。如果您不能使用HAVING子句，您可以尝试通过运行以下语句来模拟它，这应该是非常标准的：

SELECT
Officer.id as OfficerID,
Officer.name,
COUNT(Report.id) as reports
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 BETWEEN 20140323000000 AND 20140401235959)
GROUP BY Officer.id, Officer.name;

然后应用其中reports等于0的滤波器，即在给定时间帧中没有报告。您可以添加MAX(performanceDate) AS performanceDate, MAX(Report.id) AS ReportID，以获得那些在时间范围外至少有一份报告的官员的报告日期(例如最新)。这可能不是你想要的报告。

指定日期范围时必须小心，因为YYYYMMDD通常等于YYYYMMDD000000，这可能导致相当于半包含范围。否则，将BETWEEN替换为performanceDate >= '2014-03-23 00:00:00' AND performanceDate <= '2014-04-01 23:59:59'。

Answer 3

感谢大家在这个问题上的帮助。我的最终解决方案是使用GROUP BY和HAVING子句。

@Iserni，我不需要SELECT一个在时间范围内有0个报告的官员，我可以使用HAVINGSELECT所有在时间范围外有报告的官员或有空报告的官员。

这是我的最终代码：

SELECT Officer.id AS OfficerID, Officer.name, Report.id AS ReportID, max(performanceDate) as maxPerfDate FROM Officer
LEFT JOIN report_officer ON Officer.id = report_officer.officer_id
LEFT JOIN Report ON Report.id = report_officer.report_id
GROUP BY Officer.id HAVING maxPerfDate is null OR 
maxPerfDate < "2014-03-23 00:00:00" OR
maxPerfDate > "2014-04-01 00:00:00";

这样做的好处是，我可以利用绩效日期报告官员上次提交报告或报告时从未创建报告的时间。建议的所有其他解决方案都取消了检索官员上次创建报告时的有价值信息的能力。

Answer 4

添加另一个AND条件可能会解决您的问题。

AND performanceDate NOT BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00"

Answer 5

或者您可以排除满足条件的记录。。。

SELECT *
FROM OFFICER
WHERE ID NOT IN (SELECT OFFICER_ID
FROM REPORT_OFFICER)
OR ID NOT IN (SELECT OFFICER_ID
FROM REPORT_OFFICER
WHERE performanceDate BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00")

Answer 6

您可以像下面这样使用WHERE NOT EXISTS语句吗？

SELECT *
FROM Officer
WHERE NOT EXISTS
(
SELECT Report.ID
FROM 
Report_Officer
INNER JOIN 
Report ON 
Report_Officer.Report_ID = Report.ID
WHERE 
Report_Officer.Officer_ID = Officer.ID AND
Report.PerformanceDate BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00"
)