php - 获取每组前三名的比赛记录

Question

我运行这个查询，它给了我正确的数据。唯一的问题是我想要每组的前 3 条记录；这会给我总记录。

SELECT division_teams.division_id,division_teams.team_id,seasons.name,SUM(team_points.total) AS points 

FROM seasons 
INNER JOIN division_teams ON division_teams.season_id = seasons.id 
INNER JOIN team_points ON 
     eam_points.team_id = division_teams.team_id 
WHERE seasons.id = 19 
GROUP BY 
     division_teams.division_id,team_points.team_id 
ORDER BY division_id ASC,points DESC

表输出

标签： phpmysqllaravel

Answer 1

将自联接与子查询一起使用

SELECT A.devision_id, A.team_id,A.name,a.points, COUNT(lesser.point) AS rank
FROM 
(SELECT division_teams.division_id,division_teams.team_id,seasons.name,SUM(team_points.total) AS points FROM seasons INNER JOIN division_teams ON division_teams.season_id = seasons.id INNER JOIN team_points ON eam_points.team_id = division_teams.team_id 
WHERE seasons.id = 19 GROUP BY division_teams.division_id,team_points.team_id
) A 
LEFT JOIN 
(SELECT division_teams.division_id,division_teams.team_id,seasons.name,SUM(team_points.total) AS points FROM seasons INNER JOIN division_teams ON division_teams.season_id = seasons.id INNER JOIN team_points ON eam_points.team_id = division_teams.team_id 
WHERE seasons.id = 19 GROUP BY division_teams.division_id,team_points.team_id
) AS lesser 
ON A.division_id = lesser.division_id  AND A.points < lesser.point
GROUP BY A.devision_id, A.team_id,A.name,a.points
HAVING COUNT(lesser.point) < 3
ORDER BY A.division_id ASC,A.points DESC