嵌套表的SQL Join返回错误数据

Question

尽管我认为自己对数据库操作很有经验，但下面的行为使我感到困惑。

我有两个表，一个用于国家，另一个用于每个国家每个日期的covid病例。基本上是这样的:

countries:

<表类> id 名称 tbody><<tr>1阿根廷2巴西3古巴4厄瓜多尔

如果您正在运行MariaDB 10.2或更高版本，您可以在CTE中使用ROW_NUMBER()来获取每个country_id的最新案例数据，然后可以将其连接到countries以获得国家名称:

WITH latest AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY country_id ORDER BY date DESC) AS rn
FROM cases
)
SELECT c.name, l.active_cases, l.date
FROM latest l
JOIN countries c ON c.id = l.country_id
WHERE l.rn = 1

输出:

name        active_cases    date
Argentina   30              2021
Brazil      300             2021
Cuba        3000            2021
Ecuador     30000           2021

您的内部查询按country_id分组并对DESC进行排序。根据表中的数据，它为您提供每个示例中2021的所有结果的事实是巧合的。这就是为什么您的联接查询返回意外数据的原因。

如果你正在寻找最新的案例，那么你应该在你的内部查询中添加一个显式的ORDER BY，如下所示:

SELECT countries.*, active_cases, date
FROM countries
JOIN (
SELECT cases.country_id, active_cases, date
FROM cases
JOIN (
SELECT country_id, MAX(date) as target_date
FROM cases
GROUP BY country_id 
) as latest
ON cases.country_id = latest.country_id
AND cases.date = latest.target_date
) as latest_cases
ON countries.id = latest_cases.country_id;

编辑:使用嵌套子查询替换查询，但上面的答案适用于更干净的行分区。