php - MySQL从结果表中显示球队排名

Question

我有两个表:

teams
---------------------
team_id     team_name
---------------------
 1          Lakers
 2          Clippers
 3          Grizzlies
 4          Heat

results
...............................................................
game_id     team_id1    team_id2     team1_score    team2_score
   1            1           2             20             30  
   2            1           3             40             50
   3            2           1             50             60
   4            4           2             20             30
   5            1           2             20             30

我的问题是，如何根据按分数排序的结果为该表创建排名结果，如下所示:

...............................................................
Position    team_name    total_points    games_played
   1        Lakers           140               4
   2        Clippers         110               3 
   3        Grizzlies        50                1 
   4        Heat             20                1 

Answer 1

我猜你想做的是这样的:

已编辑

SET @num :=0; 
SELECT (@num := @num + 1) as Position,team_name,total_points,games_played
FROM (
  SELECT teams.team_name, SUM(p) as total_points, count(*) as games_played
    FROM (
      SELECT team_id1 as id, team1_score as p FROM results
      UNION ALL
      SELECT team_id2 as id, team2_score as p FROM results
   ) t
   INNER JOIN teams ON t.id = teams.team_id
   GROUP BY id,teams.team_name ) t2
ORDER BY total_points DESC;

SQLFiddle 在这里:http://www.sqlfiddle.com/#!2/5bf2c/1

如果你想显示所有球队，即使有些球队没有打一场比赛，你也可以这样:

SET @num :=0; 
SELECT (@num := @num + 1) as Position,team_name,total_points,games_played
FROM (
  SELECT
    teams.team_name,
    SUM(p) as total_points,
    SUM(f) as games_played 
    FROM (
    SELECT team_id1 as id, team1_score as p, 1 as f FROM results
    UNION ALL
    SELECT team_id2 as id, team2_score as p, 1 as f FROM results
    UNION ALL
    SELECT team_id as id, 0 as p, 0 as f FROM teams
) t
INNER JOIN teams ON t.id = teams.team_id
GROUP BY id,teams.team_name ) t2
ORDER BY total_points DESC;

SQLFiddle 在这里:http://www.sqlfiddle.com/#!2/8ecf5d/9

希望这有帮助。