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

我有2个多对多的表,还有一个表来加入它们.

官

> id

>名字

报告

> id

> performanceDate

>标题

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小提琴：http://sqlfiddle.com/#!2/1bf72/3< - 在sql小提琴中,请参阅我想要返回的列的'name'字段.

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

理想情况下,我希望尽可能简单地使用我的ORM.我正在使用doctrine并且不想开始使用完全自定义的代码(所以如果只使用连接可以完成,那就太好了).我有一种不好的感觉,我需要一个子查询.

解决方法:

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子句并反转约束.

如果要删除重复项,可以通过以下方式尝试组：

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 JOINs所不能实现的……

常用的是子查询解决方案：

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"

)

但这些并不是那么高效……这个看起来是否有报告禁止输出行.

标签：mysql,sql,doctrine-orm

来源： https://codeday.me/bug/20190612/1226186.html