MySQL:WHERE 子选择中 GROUP BY 的奇怪行为

Question

我希望你能帮我解决这个问题。

我有一张表，相关字段是VARCHAR id、VARCHAR名称和日期

3DF0001AB    TESTING_1    2017-04-04
3DF0002ZG    TESTING_2    2017-04-03
3DF0003ER    TESTING_1    2017-04-01
3DF0004XY    TESTING_1    2017-03-26
3DF0005UO    TESTING_3    2017-03-25

目标是为每个名称 (>500) 检索两个条目，按日期排序。因为我只能使用数据库查询，所以我尝试了以下方法。为每个名称获取一个 ID，使用相同的查询 UNION 结果，但排除第一组中的 ID。

第一步是为每个名称获取一个条目。结果符合预期，每个名字一个 id。

SELECT id FROM table GROUP BY name;

第二步；在 WHERE 子句中使用上述语句来接收不在第一个结果中的结果:

SELECT id FROM table WHERE id NOT IN (SELECT id FROM table GROUP BY name)

但这里的结果是空的，然后我尝试使用 WHERE id IN 而不是 NOT IN 来反转 WHERE。预期结果是仅使用子查询时会出现相同的 ID，结果是表中的所有 ID。所以我假设子查询提供了错误的结果，因为当我手动复制 ID 时 -> id IN ("3DF0001AB", ...) 它有效。

所以也许有人可以解释行为和/或帮助找到原始问题的解决方案。

Answer 1

这是一个非常糟糕的做法:

SELECT id
FROM table
GROUP BY name;

尽管 MySQL 允许此构造，但返回的 id 来自不确定 行。当您在不同的时间运行相同的查询时，您甚至可以获得不同的行。

更好的方法是使用聚合函数:

SELECT MAX(id)
FROM table
GROUP BY name;

不过，您真正的问题略有不同。使用 NOT IN 时，如果 IN 列表中的任何 值为NULL，则不会返回任何行。这就是 NOT IN 的定义方式。

我建议改用 NOT EXISTS 或 LEFT JOIN，因为它们的行为更直观:

SELECT t.id
FROM table t LEFT JOIN
     (SELECT MAX(id) as id
      FROM table t2
      GROUP BY name
     ) tt
     ON t.id = tt.id
WHERE tt.id IS NULL;