我有一个用户表,其中包含他们投票的用户 ID,如下所示:
uid | voted_for
1 | 3
2 | 3
3 | 1
我的目标是根据有多少人投票给该 uid 来订购 uid。但我不知道该怎么做。
所以最终的结果是:
uid | Total_Votes
3 | 2
1 | 1
2 | 0
希望您能帮助解释为此构建 SQL 的最佳方法。
最佳答案
这个简单的查询将产生您请求的输出:
select voted_for as uid, count(*) as total_votes
from users
group by 1
order by 2 desc
如果您希望输出中有关每个用户的所有数据,请将用户加入自身:
select u.*, count(v.uid) as total_votes
from users u
left join users v on v.voted_for = u.uid
group by 1,2,3,4,5 -- put as many numbers here as there are columns in the users table
order by total_votes desc
如果没有人为用户投票,第二个查询将给出
total_votes
分数为零。或者,您可以只选择您想要的那些列:
select u.uid, u.name, count(v.uid) as total_votes
from users u
left join users v on v.voted_for = u.uid
group by 1,2
order by 3 desc
``
要仅返回获胜者,请执行以下操作:
select u.uid, u.name, count(*) as total_votes
from users u
left join users v on v.voted_for = u.uid
group by 1,2
having count(*) = (
select max(c) from (
select count(*) as c from users group by voted_for))
order by 3 desc
关于mysql - 按总计数顺序选择行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/16782314/