mysql - 每个城市的最新年份和项目

我有两张桌子。一个有市镇（wsjg_mun），另一个有项目（wsjg_proj）。我需要每个市最近一年（yr）和项目（proj_id）。

Table wsjg_mun (municipalities)

    pol_id (hash version of municipality name)
    mun (municipality name)
    cbs_cde
    cbs_id (municipality id, PK)

Table wsjg_proj (projects)

    cbs_id (municipality id)
    yr (year project)
    edition
    proj_id (project)
    lud

此查询

SELECT a.pol_id, a.mun, a.cbs_cde, a.cbs_id,  yr, b.edition FROM wsjg_proj b
LEFT JOIN wsjg_mun a
ON a.cbs_id = b.cbs_id
GROUP BY b.cbs_id, b.yr
ORDER BY  b.cbs_id , b.yr asc

结果：

Bedum     2010  BP
Bedum     2011  BR
Bedum     2014  BP
Ten Boer  2011  BR
Ten Boer  2013  BP

我想要

Bedum     2014  BP
Ten Boer  2013  BP

最佳答案

我认为这样的查询会很有用：

SELECT a.mun, b.yr, b.edition
FROM wsjg_mun a
  LEFT JOIN wsjg_proj b ON a.cbs_id = b.cbd_id
WHERE b.yr = (
        SELECT Max(bi.yr)
        FROM wsjg_proj bi
        WHERE bi.cbs_id = b.cbs_id)