MySQL:获取所有 2 年内未接受过任何培训的用户

Question

我需要获取过去两年内未接受过任何培训的所有用户。

我们每年开设 3 门类(class):1 月开设基础 1，2 月开设基础 2，3 月开设基础 3。

并非每个人都需要每年都参加培训，但每个人都必须在过去 2 年内完成类(class)才能保留注册资格。目前我们查看每个用户记录并突出显示用户是否在过去 2 年内没有参加过培训类(class)，但是随着用户数量的增加，我们需要一次检查所有用户所以我一直在查看查询如果他们在过去 2 年内未完成任何培训，则可能会提供数据并显示他们上次培训的日期和类(class)名称。

例如，这里有两个表:

users_temp:
+----+-------+
| id | name  |
+----+-------+
| 1  | David |
| 2  | John  |
| 3  | Barry |
| 4  | Mary  |
+----+-------+

courses_temp:
+---------+------------+---------+
| user_id | date       | name    |
+---------+------------+---------+
| 1       | 2015-01-01 | Basic 1 |
| 1       | 2015-02-02 | Basic 2 |
| 1       | 2015-03-03 | Basic 3 |
| 2       | 2015-01-01 | Basic 1 |
| 2       | 2014-02-02 | Basic 2 |
| 2       | 2014-03-03 | Basic 3 |
| 3       | 2012-01-01 | Basic 1 |
| 3       | 2012-02-02 | Basic 2 |
| 3       | 2013-03-03 | Basic 3 |
| 4       | 2013-01-01 | Basic 1 |
| 4       | 2012-02-02 | Basic 2 |
| 4       | 2012-03-03 | Basic 3 |
+---------+------------+---------+

我可以手动检查并判断 David 最后一次参加培训是在今年 3 月，John 最后一次参加是在今年 1 月，Barry 在 2013 年 3 月参加了培训，而 Mary 在 2013 年 1 月参加了培训，所以我需要制作一个像这样的表格:

+---------+-------+---------------+-------------+
| user_id | Name  | Last Training | Last Course |
+---------+-------+---------------+-------------+
| 3       | Barry | 2013-03-03    | Basic 3     |
| 4       | Mary  | 2013-01-01    | Basic 1     |
+---------+-------+---------------+-------------+

我的第一个查询是这样的:

SELECT 
    user_id, 
    max(date)
FROM 
    courses_temp
GROUP BY 
    user_id
HAVING
    max(date) < DATE_SUB(NOW(),INTERVAL 2 YEAR)

要获得这些结果:

+---------+------------+
| user_id | max(date)  |
+---------+------------+
| 3       | 2013-03-03 |
| 4       | 2013-01-01 |
+---------+------------+

获取我添加的用户的名字:

SELECT 
    user_id, 
    max(date),
    users_temp.name
FROM 
    courses_temp
JOIN
    users_temp
ON
    courses_temp.user_id = users_temp.id
GROUP BY 
    user_id
HAVING
    max(date) < DATE_SUB(NOW(),INTERVAL 2 YEAR)

要获得这些结果:

+---------+------------+-------+
| user_id | max(date)  | name  |
+---------+------------+-------+
| 3       | 2013-03-03 | Barry |
| 4       | 2013-01-01 | Mary  |
+---------+------------+-------+

为了尝试获取上一门类(class)的名称，我使用了这个

SELECT 
    user_id, 
    max(date),
    users_temp.name,
    courses_temp.name
FROM 
    courses_temp
JOIN
    users_temp
ON
    courses_temp.user_id = users_temp.id
GROUP BY 
    user_id
HAVING
    max(date) < DATE_SUB(NOW(),INTERVAL 2 YEAR)

要获得这些结果:

+---------+------------+-------+---------+
| user_id | max(date)  | name  | name    |
+---------+------------+-------+---------+
| 3       | 2013-03-03 | Barry | Basic 1 |
| 4       | 2013-01-01 | Mary  | Basic 1 |
+---------+------------+-------+---------+

但那是错误的，应该是:

+---------+------------+-------+---------+
| user_id | max(date)  | name  | name    |
+---------+------------+-------+---------+
| 3       | 2013-03-03 | Barry | Basic 1 |
| 4       | 2013-01-01 | Mary  | Basic 3 |
+---------+------------+-------+---------+

请问如何获得正确的类(class)名称(Basic 3)？

Answer 1

看起来你把事情复杂化了。 left join 负责查找最近参加的培训 - 如果 courses_temp c2 中没有行具有相同的 id，并且日期比 中的相应行更新code>courses_temp c1，您将获得空值，这使我们能够识别每个用户的最新行。之后是肉汁。

select u.*, c1.name, c1.date
  from users_temp u
    inner join courses_temp c1
      on u.id = c1.user_id
    left join courses_temp c2
      on u.id = c2.user_id and c1.date < c2.date
  where c2.date is null and c1.date < now() - interval 2 year;

你的最后一次尝试是一个很好的例子，说明 mysqls 有用的 group by 处理有点麻烦。您不能依赖它为 group by 约束中不存在的或未在聚合公式中使用的任何字段选择正确的值。参见 12.16.3 MySQL Handling of GROUP BY了解更多详情

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause [ ... ] You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.