如何在SQL关联查询中基于最新日期筛选对应companies列？

尝试关联表a和表b时，当前查询会获取companies列的第一条结果，导致数据不准确。需要让a.companies返回对应a.dates中最新日期的结果。

表结构与数据 #

表a #

表b #

现有错误查询 #

select a.companies, b.locations, b.job
where a.locations=b.locations
group by b.job

当前错误结果 #

期望结果 #

解决方案 #

可以使用窗口函数ROW_NUMBER()对每个locations下的记录按dates倒序排序，取排序为1的最新记录，再关联表b：

WITH latest_company AS (
    SELECT 
        locations,
        companies,
        ROW_NUMBER() OVER (PARTITION BY locations ORDER BY STR_TO_DATE(dates, '%m/%d') DESC) AS rn
    FROM a
)
SELECT 
    lc.companies,
    b.locations,
    b.job
FROM latest_company lc
JOIN b ON lc.locations = b.locations
WHERE lc.rn = 1;

说明 #

1. STR_TO_DATE(dates, '%m/%d')用于将字符串格式的日期转换为可排序的日期类型，确保排序逻辑准确。如果你的dates字段本身就是日期类型，直接用dates DESC即可。
2. PARTITION BY locations按地点分组，ORDER BY ... DESC让每组内最新的日期排在最前，ROW_NUMBER()给每组记录编号，最新的记录编号为1。
3. 最后关联表b，只筛选编号为1的记录，就能得到每个地点最新日期对应的公司。

如果你的数据库不支持CTE（公共表表达式），可以改用子查询实现：

SELECT 
    lc.companies,
    b.locations,
    b.job
FROM (
    SELECT 
        locations,
        companies,
        ROW_NUMBER() OVER (PARTITION BY locations ORDER BY STR_TO_DATE(dates, '%m/%d') DESC) AS rn
    FROM a
) lc
JOIN b ON lc.locations = b.locations
WHERE lc.rn = 1;

内容的提问来源于stack exchange，提问作者user23627032