MySQL查询各政党参选年份最高得票数的实现方案求助

实现方案 #

方案1：MySQL 8.0及以上版本（推荐，写法简洁） #

使用RANK()窗口函数实现，相同得票数会获得相同排名，天然支持保留同一政党多个最高得票年份的需求：

SELECT 
  p.party_name AS Name,
  e.election_year AS Year,
  r.ballots AS Ballots
FROM (
  SELECT 
    p.party_name,
    e.election_year,
    r.ballots,
    -- 按政党分组，按得票数降序排名，同得票排名一致
    RANK() OVER (PARTITION BY p.party_ID ORDER BY r.ballots DESC) AS vote_rank
  FROM Elections e
  INNER JOIN Results r 
    ON e.election_id = r.Results_Elections 
    AND e.election_type = 'Regional'
  INNER JOIN Party p 
    ON p.party_ID = r.Results_party
) t
WHERE vote_rank = 1
ORDER BY p.party_name, e.election_year;

如果你实际表中得票字段名是Results_ballots，把上面SQL里的r.ballots替换为r.Results_ballots即可。

方案2：兼容MySQL 5.x等低版本 #

先通过子查询查出每个政党的最高得票数，再关联原表筛选出所有符合最高得票的记录：

SELECT 
  p.party_name AS Name,
  e.election_year AS Year,
  r.ballots AS Ballots
FROM Elections e
INNER JOIN Results r 
  ON e.election_id = r.Results_Elections 
  AND e.election_type = 'Regional'
INNER JOIN Party p 
  ON p.party_ID = r.Results_party
-- 关联各政党的最高得票数据
INNER JOIN (
  SELECT 
    p_inner.party_ID,
    MAX(r_inner.ballots) AS max_ballots
  FROM Elections e_inner
  INNER JOIN Results r_inner 
    ON e_inner.election_id = r_inner.Results_Elections 
    AND e_inner.election_type = 'Regional'
  INNER JOIN Party p_inner 
    ON p_inner.party_ID = r_inner.Results_party
  GROUP BY p_inner.party_ID
) max_vote 
  ON p.party_ID = max_vote.party_ID 
  AND r.ballots = max_vote.max_ballots
ORDER BY p.party_name, e.election_year;

原有写法问题说明 #

• 第一段SQL只做了多表关联，没有加最高得票的筛选逻辑，所以返回了全部记录
• 第二段SQL仅按政党名称分组，没有做最大值计算逻辑，MySQL默认会返回分组后的第一条匹配记录，所以会遗漏同最高得票的其他年份记录

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