mysql - 在我的 SQL 查询中编写 LIMIT 子查询

Question

所以我有以下查询，它为一个组织获取活跃的比赛，但也旨在为每个获取的比赛获取领先的用户。

查询目前有效，因为它获取了比赛，但是它当前获取了所有用户，我想使用 SUM(activity_weight) 对获取的用户进行 LIMIT 1，您可以在下面看到。

结果是这样的(删除了一些结果以便于查看)，在我的例子中，我只想获取 John 和 Sally，因为他们是比赛的领导者。

competitionId  compName  start_date  end_date  name  totalPoints
------------------------------------------------------------
123           First Comp   13-09-09  13-10-09  John    100
123           First Comp   13-09-09  13-10-09  Bob     50
431           Second Comp  13-05-04  13-10-05  Sally   500
431           Second Comp  13-05-04  13-10-05  Jessica 50

我知道我必须使用某种形式的子查询来使用 LIMIT，但是在确定它的语法时遇到了问题。

非常感谢任何帮助!谢谢你

  SELECT c.competitionId, c.name, c.start_date, c.end_date, a.userid, u.name, 
  u.profilePic , 
  SUM(activity_weight) AS totalPoints

  FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid 
  INNER JOIN competitions c  ON c.competitionId = a.competitionId

  WHERE c.organisationId = '$organisation' AND c.start_date < now() AND c.end_date > now()  
  GROUP BY a.userid, c.competitionId ORDER BY c.id DESC, totalPoints DESC

Answer 1

试试这个查询

select * from
(select 
@rn:=if(@prv=competitionId  , @rn+1, 1) as rId,
@prv:=competitionId  as competitionId  ,
totalPoints,
your_other_columns
from (select * from ...)subquery
join
(select @prv:=0, @rn:=0)tmp
order by 
competitionId  , totalPoints desc) a
-- only top 2 ordered by points for every competition
where rid<=2

output:
rID  competitionId  compName  start_date  end_date  name  totalPoints
------------------------------------------------------------
 1      123           First Comp   13-09-09  13-10-09  John    100
 2      123           First Comp   13-09-09  13-10-09  Bob     50
 1      431           Second Comp  13-05-04  13-10-05  Sally   500
 2      431           Second Comp  13-05-04  13-10-05  Jessica 50

将最后一部分改为where rid<=1选择前 1