使用LEFT JOIN的MySQL聚合函数

Question

现在是周五下午^*，我已经没法思考了。通常情况下我会回答一些愚蠢的 SQL 问题，对不起！

我正在尝试通过将后者 LEFT JOIN 到前者来获取一个表以及另一个表中某一列的最高值。

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes ON (jobs.id=notes.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
  AND (notes.type="complete" OR notes.type IS NULL)
GROUP BY jobs.id
ORDER BY complete_date ASC

我正在尝试获取符合 WHERE jobs.… 条件的所有工作，并且如果它们有一个，获取与该工作相关联的最新type=complete注释的时间戳：

Job ID     Complete Date
1          today
2          NULL
4          yesterday

第三个工作没有出现是因为它不符合jobs.status的条件。但是实际得到的结果是：

Job ID     Complete Date
1          today
4          yesterday

Job 2 缺失，即 JOIN 像 INNER JOIN 一样运行。
我确定只是我恍惚了，但我看不出来为什么我的 LEFT (OUTER) JOIN 没有给我所有的工作，无论 note 的值如何。

具体来说，用户可以删除注释，因此可能一个完整/关闭的工作可能没有 type=complete 的注释（当状态改变时输入注释），我试图捕获当用户关闭一个工作、添加注释，然后删除注释的情况。

_{* 在东方的某个地方}

- Nicholas Shanks

Answer 1

既然你在WHERE子句中使用了notes表的过滤器，那么JOIN就像是一个INNER JOIN，将其移至JOIN条件中：

SELECT
  jobs.*,
  MAX(notes.`timestamp`) AS complete_date
FROM jobs
LEFT JOIN notes 
  ON (jobs.id=notes.job_id)
  AND (notes.type="complete" OR notes.type IS NULL)
WHERE (jobs.status="complete" OR jobs.status="closed")
GROUP BY jobs.id
ORDER BY complete_date ASC;

这也可以通过使用子查询来完成，因此您可以在子查询中应用注释过滤器：

SELECT
  jobs.*,
  n.complete_date
FROM jobs
LEFT JOIN
(
    select job_id, MAX(`timestamp`) AS complete_date
    from notes 
    where (type="complete" OR type IS NULL)
    group by job_id
) n
  ON (jobs.id=n.job_id)
WHERE (jobs.status="complete" OR jobs.status="closed")
ORDER BY complete_date ASC

- Taryn

---

前半部分是我所缺失的 - 大脑无法看到眼前的东西，但我已经意识到查询将需要更复杂，并且我将无法避免使用子查询。它一个小时前被写成了一个子查询（仍然不起作用）。哦，好吧，有得有失。 - Nicholas Shanks

将条件从WHERE子句移动到JOIN中解决了我在SQL Server中的问题。感谢@Taryn。 - user4089220