php - mysql - 'IN/ALL/ANY subquery' 中的未知列

Question

我有一个名为“投票”的下表，其中存储了参与者的赞成票 (1) 和反对票 (-1)。我想获得前 3 名投票的参与者 ID 及其总票数，其中总票数 = 赞成票 - 反对票。我按照下面给出的方式编写了查询，但出现错误:“‘IN/ALL/ANY 子查询’中的未知列‘total_votes’”

table 'votes'---------------------------------| participant_id | vote| voter_id+------+-------+---------+------+|              1 | 1   |  1|              2 | 1   |  1|              3 | 1   |  1|              4 | -1  |  1|              5 | 1   |  1|              1 | -1  |  2|              2 | 1   |  2|              3 | 1   |  2|              4 | 1   |  2|              5 | 1   |  2|              1 | 1   |  3|              2 | 1   |  3|              3 | -1  |  3|              4 | -1  |  3+------+-------+---------+-----+

SELECT `participant_id`, SUM( `vote` ) AS total_votes FROM `votes` 
WHERE total_votes IN  
(SELECT total_votes FROM  
(SELECT DISTINCT (SUM( vote )) AS total_votes FROM  `votes` GROUP BY  `participant_id` 
ORDER BY `total_votes` DESC LIMIT 0 , 3) AS temp )  
GROUP BY `participant_id`

Expected result would be-------------------------------| participant_id | total_votes+------+-------+--------+------+|              2 | 3   |              5 | 2   |              1 | 1   |              3 | 1   +------+-------+--------+------+

Answer 1

您可以通过使用 join 来重写您的查询，以获得投票得分在前 3 票中的顶级参与者，即前 3 票是 (3,2,1)

SELECT t.* FROM
(SELECT `participant_id`, SUM( `vote` ) AS total_votes 
FROM `votes` 
GROUP BY `participant_id`
) t
JOIN  (SELECT SUM( `vote` ) AS total_votes FROM `votes` 
GROUP BY `participant_id`
ORDER BY `total_votes` DESC LIMIT 0 , 3 ) t1
USING(total_votes)
ORDER BY t.total_votes DESC

Fiddle Demo