Mysql多对多查询

Question

我的 mysql 数据库中有多对多表设置。球队可以参加多场比赛，每场比赛有 2 支球队。它们之间有一个名为 team_games 的表。显示创建表信息如下。

我已经搞乱这个查询有一段时间了。此时我不关心它是否需要子查询、连接或联合。我已经尝试了所有这些，但没有一个是令人满意的，我想我错过了一些东西。我一直遇到的问题是从每场比赛中找到两个球队 ID，然后使用 tid 来获取球队信息。

我想做的是，如果给定一个游戏ID（gid），我可以查询找到：

的所有信息

home_team_name，home_team_id，away_team_name，away_team_id，team_league（客场和主场将是同一个联赛），来自游戏表表创建表

 teams  CREATE TABLE `teams` (
 `tid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(60) NOT NULL,
`league` varchar(2) NOT NULL,
`active` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`)

)

> <代码> 创建表teams_games (

`tid` int(10) unsigned NOT NULL,
`gid` int(10) unsigned NOT NULL,
`homeoraway` tinyint(1) NOT NULL,
PRIMARY KEY (`tid`,`gid`),
KEY `gid` (`gid`),
CONSTRAINT `teams_games_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teams` (`tid`),
CONSTRAINT `teams_games_ibfk_2` FOREIGN KEY (`gid`) REFERENCES `games` (`gid`)

)

> <代码> 创建表游戏 (

`gid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`location` varchar(60) NOT NULL,
`time` datetime NOT NULL,
`description` varchar(400) NOT NULL,
`error` smallint(2) NOT NULL,
`home_score` smallint(2) DEFAULT NULL,
`away_score` smallint(2) DEFAULT NULL,
PRIMARY KEY (`gid`)

)

Answer 1

为什么不直接删除 teams_games 表并更改 games：

CREATE TABLE games (

 `gid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`location` varchar(60) NOT NULL,
`time` datetime NOT NULL,
`description` varchar(400) NOT NULL,
`error` smallint(2) NOT NULL,
`home_score` smallint(2) DEFAULT NULL,
`away_score` smallint(2) DEFAULT NULL,
`home_tid` int(10) unsigned NOT NULL,
`away_tid` int(10) unsigned NOT NULL,
PRIMARY KEY (`gid`)
)

然后您可以编写一个简单的联接，例如：

SELECT 
    g.*, 
    h.name as home_team, 
    a.name as away_team, 
    h.league as league 
FROM games AS g 
    INNER JOIN teams AS h ON g.home_tid = h.tid
    INNER JOIN teams as a ON g.away_tid = a.tid
WHERE gid = ?

Answer 2

我假设 homeoraway = 1 表示主场，homeoraway = 0 表示客场。

SELECT g.*, ht.name, ht.tid, at.name, at.tid, ht.league
FROM games g
JOIN team_games htg ON htg.gid = g.gid AND htg.homeoraway = 1
JOIN team ht ON ht.tid = htg.tid
JOIN team_games atg ON atg.gid = g.gid AND atg.homeoraway = 0
JOIN team at ON at.tid = atg.tid

这是通过将主队的比赛加入到 team_games 中，然后加入到球队中来获取球队信息，然后为客队做同样的事情来实现的。

Answer 3

我假设 1 = 在家，2 = 离开。你可以适当改变。

SELECT
    HT.name AS home_team_name,
    HT.tid AS home_team_id,
    AT.name AS away_team_name,
    AT.tid AS away_team_id,
    HT.league AS team_league
FROM
    teams_games HTG
INNER JOIN teams_games ATG ON
    ATG.gid = HTG.gid AND
    ATG.homeoraway = 2
INNER JOIN teams HT ON
    HT.tid = HTG.tid
INNER JOIN teams AT ON
    AT.tid = ATG.tid
WHERE
    HTG.gid = ???
    HTG.homeoraway = 1

Answer 4

select 
  th.name as home_team_name,
  th.tid as home_team_id,
  ta.name as away_team_name,
  ta.tid as away_team_id,
  th.league as team_league,
  g.* 
from games g
  inner join teams_games tgh on (g.gid = tgh.gid and tgh.homeoraway = <HOME_VALUE>)
  inner join teams_games tga on (g.gid = tga.gid and tga.homeoraway = <AWAY_VALUE>)
  inner join teams th on (tgh.tid = th.tid)
  inner join teams ta on (tga.tid = ta.tid)
where
  g.gid = <GAME_ID>