php - MySQL 中复杂的足球联赛动态排序？

Question

我有一个足球联赛的“比赛”表，如下所示:

date    home_team_id    away_team_id    home_score      away_score
 -          1                 2              6             21
 -          3                 1              7             19

我不知道如何动态生成按获胜顺序排序的团队 ID 列表(如果获胜则得分)？

--

我有这个查询，当我有一个 $team_id 时它工作正常，但因为我一次只能做 1 个团队，并且不允许在查询级别排序

((SELECT COUNT(*) FROM `games` WHERE ((`home_score` > `away_score`) AND `home_team_id` = '.$team_id.')) + 
(SELECT COUNT(*) FROM `games` WHERE ((`home_score` < `away_score`) AND `away_team_id` = '.$team_id.'))) AS `wins`

我想知道我是否可以将它与某种形式的 GROUP 一起使用，或者 mySQL 可以知道 $team_id 本身？我还尝试了一些与“团队”表的多个 JOIN，但它们也没有用。

谢谢，

丹

Answer 1

让我们一步一步来:

选择主场胜场和主场比分:

   SELECT COUNT(*) as wins, SUM(G.home_score) as score FROM games G WHERE 
      G.team_id = T.team_id #See 3. query and you'll understand
      G.home_score > away_score

我们称此结果为 HOME_GAMES。

选择胜场和客场比分:

SELECT COUNT(*) as wins, SUM(G.away_score) as score FROM games G
WHERE 
  G.team_id = T.team_id #See 3. query and you'll understand
  G.away_score > G.home_score

我们称此结果为 AWAY_GAMES。

选择总胜局和总分:

   SELECT (A.wins + H.wins) AS total_wins, (A.score + H.score) AS total_score FROM
   (AWAY_GAMES) AS A, (HOME_GAMES) AS H, teams T 
   ORDER BY total_wins, total_score

==> 通过替换 AWAY_GAMES 和 HOME_GAMES 放在一起:

SELECT (A.wins + H.wins) AS total_wins, (A.score + H.score) AS total_score FROM 
  (SELECT COUNT(*) as wins, SUM(G.away_score) as score FROM games G
   WHERE 
     G.team_id = T.team_id #See 3. and you'll understand
     G.away_score > G.home_score) AS A, 

   (SELECT COUNT(*) as wins, SUM(G.home_score) as score FROM games G 
   WHERE 
      G.team_id = T.team_id #See 3. and you'll understand
      G.home_score > away_score) AS H, 

   teams T
   ORDER BY total_wins, total_score