Mysql 查询 Union All 和 Join for League 表

Question

对于足球联赛，我有两张 table :

• “teams”是一个表格，其中将所有团队列为唯一条目(“id”，'team_name')
• “matches”是一个显示匹配结果的表格:在列中“主场”和“客场”我保存每场比赛的结果。 'home_team_id' 和“away_team_id”是与团队表的关联。

这就是我的查询的样子:

SELECT Teams, Sum(P) as 'Matches', Sum(W) as 'win', Sum(D) as 'draw', Sum(L) as 'lost',
       SUM(Pts) as 'points'
  FROM (
        SELECT home Teams, 1 P,
                IF (home > away,1,0) W,
                IF (home = away,1,0) D,
                IF (home < away,1,0) L,
                CASE 
                    WHEN home > away THEN 3
                    WHEN home = away THEN 1 
                    ELSE 0
                END PTS
          FROM `matches`

        UNION ALL
        SELECT away Teams, 1,
                IF (home < away,1,0),
                IF (home = away,1,0),
                IF (home > away,1,0),
                CASE 
                    WHEN home < away THEN 3
                    WHEN home = away THEN 1
                    ELSE 0
                END
          FROM `matches`
        ) AS ERG
GROUP BY Teams
ORDER BY SUM(Pts) DESC 

现在我想要团队表中的团队名称(teams.team_name)。为了实现这一点，我尝试了几个连接语句，但没有成功。

很明显，球队表可以包含没有参加比赛的球队。这些球队需要以零结果来展示。

因此我尝试了 LEFT JOIN:

SELECT team_name AS 'Teams'
  FROM `teams`
  LEFT JOIN matches ON ( teams.id = matches.home_team_id )

就在最后的 ORDER 行之后。我收到一条错误消息。我使用 MySQL 5.1.44，因此嵌套选择应该不是问题。

有什么想法吗？

Answer 1

听起来你想做这样的事情。我更新了您的子查询以包含 home_team_id 和 away_team_id，然后您将在 teams 表上JOIN返回名称:

SELECT Teams, 
  Sum(P) as 'Matches',
  Sum(W) as 'win',
  Sum(D) as 'draw',
  Sum(L) as 'lost',
  SUM(Pts) as 'points',
  h.team_name as HomeTeam,
  a.team_name as AwayTeam
FROM
(
  SELECT home Teams,
    1 P,
    IF (home > away,1,0) W,
    IF (home = away,1,0) D,
    IF (home < away,1,0) L,
    CASE WHEN home > away THEN 3 WHEN home = away THEN 1 ELSE 0 END PTS,
    home_team_id,
    away_team_id
  FROM `matches`

  UNION ALL

  SELECT away Teams,
    1,
    IF (home < away,1,0),
    IF (home = away,1,0),
    IF (home > away,1,0),
    CASE WHEN home < away THEN 3 WHEN home = away THEN 1 ELSE 0 END,
    home_team_id,
    away_team_id
  FROM `matches`
) AS ERG
LEFT JOIN `teams` h
  on ERG.home_team_id = h.home_team_id
LEFT JOIN `teams` a
  on ERG.away_team_id = a.away_team_id
GROUP BY Teams, home_team_id, away_team_id
ORDER BY SUM(Pts) DESC 

根据您的评论编辑 #1，听起来您想要这个(请参阅 SQL Fiddle with Demo ):

SELECT TeamId, 
  t.team_name,
  Teams, 
  Sum(P) as 'Matches', 
  Sum(W) as 'win', 
  Sum(D) as 'draw', 
  Sum(L) as 'lost',
  SUM(Pts) as 'points'
FROM 
(
  SELECT home_team_id TeamId, 
      home Teams, 1 P,
      IF (home > away,1,0) W,
      IF (home = away,1,0) D,
      IF (home < away,1,0) L,
      CASE 
          WHEN home > away THEN 3
          WHEN home = away THEN 1 
          ELSE 0
      END PTS
  FROM `matches`

  UNION ALL
  SELECT away_team_id TeamId,
       away Teams, 1,
       IF (home < away,1,0),
       IF (home = away,1,0),
       IF (home > away,1,0),
       CASE 
           WHEN home < away THEN 3
           WHEN home = away THEN 1
           ELSE 0
        END
   FROM `matches`
) AS ERG
LEFT JOIN `teams` t
  ON ERG.TeamId = t.id
GROUP BY Teams, TeamId
ORDER BY SUM(Pts) DESC