MySQL中复杂的足球联赛动态订购? [英] Complicated football league Dynamic Ordering in MySQL?

问题描述

我有一个足球联赛的桌子游戏"，如下所示:

I have a table 'games' for a football league as follows:

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

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

I can't figure out how to dynamically generate a list of team ID's ordered by Wins (then points for if poss)?

-

我有一个查询，当我有一个$ team_id时，它可以正常工作，但是由于某种原因，我一次只能建立1个团队，并且不允许在查询级别进行订购

I have this query which works fine when I have a $team_id but of cause then I can only do 1 team at a time, and that doesn't allow for ordering at query level

((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本身? 我还尝试过使用团队"表进行多个联接，但它们也不起作用.

I wonder if i can use this with some form of GROUP, or mySQL can know the $team_id itself? I've also tried some multiple JOINs with the 'team' table but they didn't work either.

谢谢

丹

推荐答案

也许这就是您想要的?

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:原始答案还不完整，我认为应该会更好.

ETA: Original answer wasn't complete, I think this should be better.

内部结合在一起的两个查询正在为每个团队赢得主场胜利和客场胜利.外部查询只是将主场胜利和客场胜利相加，得出总胜利次数.

The inner two queries that are UNION'd together are getting the home and away wins for each team. The outer query simply sums up the home and away wins for the total win count.

这篇关于MySQL中复杂的足球联赛动态订购?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！