MySQL使用LEFT JOIN聚合函数 [英] MySQL aggregate functions with LEFT JOIN

问题描述

今天是星期五下午 ^* ，我的大脑停止了工作.通常是我这样回答愚蠢的SQL问题，对不起！

It's Friday afternoon^* and my brain has stopped working. Normally it is me who's answering dumb SQL questions like this, sorry!

我正试图通过将左表与前一个表联接在一起，来获得一个表以及另一表的一列的最大值.

I am trying to get one table, along with the highest value of a column of another table by LEFT JOINing the latter to the former.

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职位的所有 职位.…条件，如果有，则为与该作业相关的最新 type = complete 注释的时间戳:

I am trying to get all jobs that meet the WHERE jobs.… criteria, and if they have one, the timestamp of the latest type=complete note associated with that job:

Job ID     Complete Date
1          today
2          NULL
4          yesterday

作业3未出现，因为它不符合 jobs.status 标准.但是我实际上得到的是:

Job 3 don't appear because it don't meet the jobs.status criteria. But what I actually get is:

Job ID     Complete Date
1          today
4          yesterday

缺少作业2，即JOIN的行为类似于INNER JOIN.
我敢肯定，这只是我脑筋急转弯的一刻，但我不明白为什么不管票据的价值如何，我的左(外)连接"为什么不给我所有工作.

Job 2 is missing, i.e. the JOIN is acting like an INNER JOIN.
I am sure it's just me having a brain-dead moment, but I can't see why my LEFT (OUTER) JOIN is not giving me all jobs regardless of the value of the note.

具体来说，用户可以删除便笺，因此潜在的是，已完成/已关闭的作业可能没有 type = complete 便笺(当状态更改时便会输入便笺)，我正在尝试删除在用户关闭工作，添加便笺然后删除便笺时发现情况.

Specifically, users can delete notes, so potentially a complete/closed job may not have a type=complete note on it (the notes are entered when the status is changed), I am trying to catch the case when a user closes a job, adds a note, then deletes the note.

_{*在东部某处}

推荐答案

由于在 WHERE 子句中具有 notes 表的过滤器，因此 JOIN 的行为类似于 INNER JOIN ，将其移至 JOIN 条件:

Since you have the filter for the notes table in the WHERE clause the JOIN is acting like an INNER JOIN, move it to the JOIN condition:

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;

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

This could also be done using a subquery, so you apply the notes filter inside the subquery:

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

这篇关于MySQL使用LEFT JOIN聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！