从两个更优化的表中获取MySQL的最新记录

Question

我在SQL中的查询有一些问题。我有两张桌子。

people
+----+--------+------+
| id |  name  | val2 |
+----+--------+------+
|  1 | john   |   12 |
|  2 | adam   |    5 |
|  3 | alfred |    3 |
+----+--------+------+
data
+----+----+----+-----+---------------------+
| id | v1 | v2 | v3  |        date         |
+----+----+----+-----+---------------------+
|  1 |  4 | 15 |  18 | 2020-10-16 11:15:53 |
|  1 |  2 | 12 |  17 | 2020-10-16 11:22:53 |
|  1 |  3 | 13 |  16 | 2020-10-16 11:32:53 |
|  2 |  1 | 16 |  15 | 2020-10-16 13:22:53 |
|  2 |  3 | 13 |  25 | 2020-10-16 13:42:53 |
|  2 |  4 | 12 |  35 | 2020-10-16 14:12:53 |
|  3 |  1 | 21 | 12  | 2020-10-16 14:12:53 |
|  3 |  2 | 28 | 42  | 2020-10-16 15:12:53 |
|  3 |  4 | 30 | 72  | 2020-10-16 16:12:53 |
+----+----+----+-----+---------------------+

我需要获得一个表ID，NAME，v1，v2，v3，第一个表中所有对象的新日期像这样的东西：

RESULT
+----+--------+----+----+-----+---------------------+
| id |  name  | v1 | v2 | v3  |        date         |
+----+--------+----+----+-----+---------------------+
|  1 | john   |  3 | 13 |  16 | 2020-10-16 11:32:53 |
|  2 | adam   |  4 | 12 |  35 | 2020-10-16 14:12:53 |
|  3 | alfred |  4 | 30 | 72  | 2020-10-16 16:12:53 |
+----+--------+----+----+-----+---------------------+

我需要第一张桌子上所有人的第二张桌子上的最新唱片。我试着通过这个查询做到这一点：

SELECT people.id,
people.name,
data.v1,
data.v2,
data.v3,
max(data.date)
FROM people
JOIN DATA ON people.id = data.id
GROUP BY people.id

我得到了最新的数据，但v1、v2、v3是随机的。

Answer 1

您想要data中的整行，因此此处不提供聚合选项。在大多数数据库中，查询都会失败，因为select和group by子句不一致。。。但MySQL，不知何故，不幸的是，它给了开发者足够的绳索，让他们自生自灭。您的查询运行(如果sql模式ONLY_FULL_GROUP_BY被禁用(，但实际上相当于：

SELECT people.id, people.name, ANY_VALUE(data.v1), ANY_VALUE(data.v2), ANY_VALUE(data.v3), MAX(data.date)
FROM people
JOIN data on people.id = data.id
GROUP BY people.id

现在可以清楚地看到，数据库为您提供了符合联接条件的data行的任何值，这些值可能属于，也可能不属于具有最新日期的行。

您实际上需要进行筛选，而不是分组。一个选项使用子查询：

select p.id, p.name, d.v1, d.v2, d.v3, d.date
from people p
inner join data d on d.id = p.id
where d.date = (select max(d1.date) from data d1 where d1.id = d.id)

这种方法的好处是，它适用于所有版本的MySQL，包括8.0之前的版本，在这些版本中，窗口功能不可用。

Answer 2

一个简单的方法使用窗口函数：

SELECT p.id, p.name, d.v1, d.v2, d.v3, d.date)
FROM people p JOIN
(SELECT d.*,
ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY d.date DESC) as seqnum
FROM data d
) d
ON p.id = d.id AND d.seqnum = 1;

注意：data中的联接列是id，这似乎很奇怪。我希望它被称为类似people_id的东西。