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

Question

我有一个足球联赛的“游戏”表，如下所示：

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

我不知道如何动态地生成按Wins排序的团队ID列表(然后为if poss得分)？

--

我有这个查询，当我有一个$team_id时，它工作得很好，但当然，我一次只能做一个团队，这不允许在查询级别进行排序

((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`

我想知道我是否可以将其与某种形式的组一起使用，或者mySQL可以了解$team_id本身？我还尝试了一些与'team‘表的多重连接，但它们也不起作用。

谢谢,

丹

Answer 1

也许这就是你要找的？

SELECT all_wins.team_id, SUM(all_wins.wins)
FROM (
  SELECT 
     home_team_id as team_id, 
     SUM(IF(home_score > away_score,1,0)) as wins,
     SUM(home_score - away_score) as points
  FROM games
  GROUP BY home_team_id
  UNION ALL
  SELECT 
     away_team_id as team_id, 
     SUM(IF(away_score > home_score,1,0)) as wins,
     SUM(away_score - home_score) as points
  FROM games
  GROUP BY away_team_id
) all_wins
GROUP BY all_wins.team_id
ORDER BY SUM(all_wins.wins), SUM(all_wins.points)

ETA:原始答案不完整，我认为这应该会更好。

内部的两个查询被联合在一起，得到了每个球队的主场和客场胜利。外部查询简单地将主场和客场胜利汇总为总获胜计数。

Answer 2

让我们一步一步来：

选择主场获胜的比赛和主场比分：

   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 

Answer 3

基于Eric的解决方案-如果其他人有类似的问题，这是我的最后一个问题-感谢大家的帮助。

SELECT `teams`.`id`, `teams`.`name`,
        SUM(`all_wins`.`gp`) AS `gp`,
        SUM(`all_wins`.`w`) AS `w`, SUM(`all_wins`.`l`) AS `l`, SUM(`all_wins`.`t`) AS `t`,
        SUM(`all_wins`.`ptf`) AS `ptf`, SUM(`all_wins`.`pta`) AS `pta`
FROM (
  SELECT
     `home_team_id` as `team_id`,
     COUNT(`home_score`) AS `gp`,
     SUM(IF(`home_score` > `away_score`,1,0)) as `w`,
     SUM(IF(`home_score` < `away_score`,1,0)) as `l`,
     SUM(IF(`home_score` = `away_score`,1,0)) as `t`,
     SUM(IFNULL(`home_score`,0)) as `ptf`,
     SUM(IFNULL(`away_score`,0)) as `pta`
    FROM `games`    
  GROUP BY `home_team_id`
  UNION ALL
  SELECT
     `away_team_id` as `team_id`,
     COUNT(`home_score`) AS `gp`,
     SUM(IF(`away_score` > `home_score`,1,0)) as `w`,
     SUM(IF(`away_score` < `home_score`,1,0)) as `l`,
     SUM(IF(`away_score` = `home_score`,1,0)) as `t`,
     SUM(IFNULL(`away_score`,0)) as `ptf`,
     SUM(IFNULL(`home_score`,0)) as `pta`
    FROM `games`

  GROUP BY `away_team_id`
) `all_wins`
LEFT JOIN `teams` ON `all_wins`.`team_id` = `teams`.`id`
GROUP BY `all_wins`.`team_id`
ORDER BY SUM(`all_wins`.`w`) DESC, SUM(`all_wins`.`ptf`) DESC