mysql 对另一个表上的所有行执行相同的计算

Question

我已经确定了我的查询:

SELECT * 
FROM  `participation` 
LEFT JOIN parties ON parties.id = participation.party_id
WHERE  `riding_id` = 10001
AND  `election_id` = 41
ORDER BY num_votes DESC 
LIMIT 1

这准确地产生了我想要的结果。

结果是投票最多的一方。

---

现在我想对 TABLE ridings 的每一行执行相同的查询其中包含所有 riding_id 行。有点麻烦。

我不想加入另一个表 - 但遍历每一行并在每一行上执行与上面相同的计算。

类似于:

SELECT * 
    FROM  `participation` 
    LEFT JOIN parties ON parties.id = participation.party_id
    WHERE  `riding_id` = "LOOP ALL riding_id IN ridings TABLE"
    AND  `election_id` = 41
    ORDER BY num_votes DESC 
    LIMIT 1

如有任何帮助，我们将不胜感激。

Answer 1

只使用子查询很诱人:

SELECT * 
FROM  participation LEFT JOIN
      parties
      ON parties.id = participation.party_id
WHERE  riding_id IN (SELECT riding_id FROM ridings) AND
       election_id = 41
ORDER BY num_votes DESC 

但是，您不再获得最高票数。你得到了一切。

这是一个使用变量来获取每个 riding_id 的最高投票的方法:

SELECT *
FROM (SELECT *,
             (@rn := if(@r = riding_id, @rn + 1,
                        if(@rn := riding_id, 1, 1)
                       )
             ) as seqnum
      FROM  participation LEFT JOIN
            parties
            ON parties.id = participation.party_id CROSS JOIN
            (SELECT @rn := 0, @r := -1) params
      WHERE  riding_id IN (SELECT riding_id FROM ridings) AND
             election_id = 41
      ORDER BY riding_id, num_votes DESC 
     ) pp
WHERE seqnum = 1;