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

user2656127 发表于 Dev

用户名

因此，我有以下查询，该查询可为组织获取活跃的竞赛，但也旨在获取牵头的用户-每获取一个竞赛。

该查询当前以获取比赛的方式起作用，但是当前它获取所有用户，并且我想使用下面显示的SUM（activity_weight）对所获取的用户限制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

阿德里安

试试这个查询

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