logo标签列表

在两个相关表之间选择数据，但不在特定日期范围内。

mysqlsqldoctrine-orm
4
我有两个多对多的表和一个连接它们的表。 官员
  • id
  • 姓名
报告
  • id
  • 表现日期
  • 标题
report_officer
  • 官员id
  • 报告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 中，请参考 'name' 字段，以获取需要返回的列。
有没有任何想法来使这个工作起来？
理想情况下，我希望这个查询尽可能简单地与我的 ORM 一起使用。 我正在使用 doctrine 并且不想开始使用完全定制的代码（因此，如果只能使用联接完成，则会很好）。 但是我有一个不好的预感我需要一个子查询。
1
你能否创建一个带有一些数据的 SQLFiddle 用于你的示例？ - Neels
尝试使用having代替where。 - salgua
@iserni 是的，我现在正在准备一个 SQLFiddle。谢谢。 - PressingOnAlways
在查询时间戳时，请使用包含下限（>=）- 以防万一有人在午夜实际上输入了一个时间戳（或系统将所有日期归一化为当天的开始）。 - Clockwork-Muse
6个回答
4
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"
)

但是这些并不是很高效... 这个函数会检查是否有报告应该禁止输出该行。

这个可以运行，但是有两个问题。第一，我需要获取performanceDate列中的数据 - 现在我只得到了null。第二，在我的示例中，对于记录134，我得到了3行带有null performanceDate的数据 - 它仍然没有解决重复行的问题。这个已经接近解决了！ - PressingOnAlways
你应该能够通过将PerformanceDate添加到“SELECT”列表中来获取PerformanceDate。我在底部添加了另一个查询，它应该可以消除重复项。 - Ulrich Thomas Gabor
它没有按预期工作，仍然只能获取performanceDate的NULL值 - http://sqlfiddle.com/#!2/6a29c/4 - PressingOnAlways
我更新了 SQLFiddle，以更清楚地表明我的意图。http://sqlfiddle.com/#!2/1bf72/4 你的查询仍然返回我不想要的行，而且超出范围的那些行没有日期。 - PressingOnAlways
1
不要在日期/时间/时间戳类型中使用BETWEEN。这适用于MySQL，因为您可以定义不同数量的小数秒。每当您使用“WHERE（SELECT COUNT（*）...）= 0”时，您实际上正在查看一个“WHERE NOT EXISTS”子句。 - Clockwork-Muse
显示剩余5条评论
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;

我不太了解Doctrine和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'。

我认为大家误解了我的意思。我要找的是所有还没有任何报告（NULL）的官员，或者最近没有报告的官员。当我尝试你的SQL时，结果为空。 - PressingOnAlways
是的，这就是我的意思。你可以通过寻找那些你不想要的官员来实现这一点，因为这样更容易；然后使用LEFT JOIN来获取其他官员。 - LSerni
1
我添加了 SQL Fiddle。 - PressingOnAlways
1
最好避免在日期/时间/时间戳类型中使用BETWEEN（https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common）。这也适用于MySQL，因为时间戳可以声明不同的小数秒。假设`MAX（id）`获取最近的报告，可能会返回正确的结果，但依赖于元数据知识。 - Clockwork-Muse
@Clockwork-Muse，完全正确。我没有删除BETWEEN，而是添加了一个解释（也许我应该为报告在午夜准确提交或四舍五入的情况添加一些内容）。谢谢。 - LSerni
1

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

@Iserni，我不需要在时间范围内选择一个报告为0的官员，我能够使用 HAVING 选择所有在时间范围之外或具有空报告的官员。

以下是我的最终代码：

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

这样做的好处是，我可以利用performanceDate报告警察最后一次提交报告的时间，或者报告他从未创建过报告。其他建议的解决方案都会丧失检索“警察最后一次创建报告时间”的有价值信息的能力。
0

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

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")
0
你可以像下面这样使用一个 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"
   )
可能，但有两个问题... #1，子查询通常不如连接高效，更重要的是，#2，我不确定我能否使用我的ORM干净地执行这样的子查询。 - PressingOnAlways
0

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

AND performanceDate NOT BETWEEN "2014-03-23 00:00:00" AND "2014-04-01 00:00:00"
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接