mysql - 在 mysql 中查找多个分组的第二高值

Question

我目前正在 mysql 中开发一个选举数据库，并正在寻找有关查询的一些帮助。目前，我可以使用查询找到所有选举的获胜者:

SELECT DISTINCT c.Office, c.Election, c.fName, c.mName, c.lName, MAX(v.votes) as Votes
-> FROM candidates as c, (SELECT DISTINCT c_id, COUNT(c_id) as votes FROM votes
-> GROUP BY c_id) as v
-> WHERE c.c_id = v.c_id
-> GROUP BY Office, Election;

现在我正在寻找每次选举的 runner 。我尝试了以下查询:

SELECT DISTINCT c.Office, c.Election, c.fName, c.mName, c.lName, MAX(v.votes) as Votes
-> FROM candidates as c, (SELECT DISTINCT c_id, COUNT(c_id) as votes FROM votes
-> GROUP BY c_id) as v, (SELECT DISTINCT c_id, COUNT(c_id) as votes FROM votes
-> GROUP BY c_id) as v2
-> WHERE c.c_id = v.c_id
-> AND v.votes < v2.votes
-> GROUP BY Office, Election;

成功返回第一次选举的 runner ，但返回所有后续选举的获胜者。如何改进我的查询，以便将 runner 条件应用于集合中的所有选举？

编辑:这是针对 mysql 5.1

Answer 1

我猜您缺少投票表之间的连接条件。请尝试以下 -

SELECT DISTINCT c.Office
               ,c.Election
               ,c.fName
               ,c.mName
               ,c.lName
               ,MAX(v.votes) as Votes
FROM candidates as c
JOIN (SELECT c_id
            ,COUNT(c_id) as votes
      FROM votes
      GROUP BY c_id) as v ON c.c_id = v.c_id
JOIN (SELECT c_id
            ,COUNT(c_id) as votes
      FROM votes
      GROUP BY c_id) as v2 ON v.c_id = v2.c_id
                          AND v.votes < v2.votes
GROUP BY c.Office
        ,c.Election
        ,c.fName
        ,c.mName
        ,c.lName

此外，我已将您的旧连接语法转换为新的 ANSI 样式连接，这更易于阅读。