This question already has answers here:
Using LIMIT within GROUP BY to get N results per group?
                                
                                    (14个回答)
                                
                        
                                2年前关闭。
            
                    
这不同于标记为双打的那个,我想总结每支球队的前5名。对于每个结果,双引号都在单独的行中取出。

我现在正在使用此问题,但似乎SQL随机返回了5个,例如10行并加总,而不是前5个。有人对我有输入吗?

选择团队,总和(长度)作为总得分

   (选择t。*,
       @num_in_group:=当@team!= team时,然后@num_in_group:= 0的情况,否则@num_in_group:= @ num_in_group + 1以num_in_group结尾,
       @team:=团队为t
    从reg_catches t,(选择@team:=-1,@num_in_group:= 0)初始化
    ORDER BY团队asc)子
WHERE sub.num_in_group GROUP BY团队
ORDER BY totalScore DESC;

我正在努力解决无法解决的SQL问题。我的结果表如下所示,我试图总结每个团队的前5名结果,并将输出限制为排名前3名的最高团队。一切都按预期进行,直到我在结果表中添加了我的最后一个分数。现在,SQL 25的输出对于第25团队是随机的。我期望是520。

team length competition
----------------------
26   70       16
25   70       16
25   95       16
25   98       16
25   100      16
25   100      16
25   100      16
25   122      16


输出:

team totalScore
---- -----------
25  122
26  70


想要的输出:

team totalScore
---- -----------
25  522
26  70


SELECT team, SUM(length) AS totalScore
FROM(
  SELECT team, length
  FROM table_result m
  WHERE competition = 16 and (
    SELECT COUNT(*)
    FROM table_result mT
    WHERE mT.team = m.team AND mT.length >= m.length
    ) <= 5) tmp
GROUP BY team
ORDER BY totalScore DESC Limit 3


有人对我有什么想法吗?

最佳答案

select team, sum(length)
from
   (SELECT t.*,
       @num_in_group:=case when @team!=team then @num_in_group:=0 else @num_in_group:=@num_in_group+1 end as num_in_group,
       @team:=team as t
    FROM test.table_result t, (select @team:=-1, @num_in_group:=0) init
    ORDER BY team, length desc) sub
WHERE sub.num_in_group<=4
GROUP BY team

09-11 17:59
查看更多