时间戳

首页 > 解决方案 > 联接三个表，以便可以替换多个列值

mysql - 联接三个表，以便可以替换多个列值

问题描述

我有三个表，conference，game和team。以下是每个的定义：

CREATE TABLE `game` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `home_team` int(11) NOT NULL,
  `away_team` int(11) NOT NULL,
  `winner` int(11) DEFAULT NULL,
  `home_conference` int(11) DEFAULT NULL,
  `away_conference` int(11) DEFAULT NULL,
  `week` int(5) NOT NULL,
  `confidence` int(5) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_game_home_team` (`home_team`),
  KEY `fk_game_away_team` (`away_team`),
  KEY `fk_game_winner` (`winner`),
  KEY `fk game_home_conference` (`home_conference`),
  KEY `fk game_away_conference` (`away_conference`),
  CONSTRAINT `fk game_away_conference` FOREIGN KEY (`away_conference`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk game_home_conference` FOREIGN KEY (`home_conference`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_away_team` FOREIGN KEY (`away_team`) REFERENCES `team` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_home_team` FOREIGN KEY (`home_team`) REFERENCES `team` (`id`) ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_winner` FOREIGN KEY (`winner`) REFERENCES `team` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `conference` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

CREATE TABLE `team` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `conference_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_team_conference_conferenceid` (`conference_id`),
  CONSTRAINT `fk_team_conference_conferenceid` FOREIGN KEY (`conference_id`) REFERENCES `conference` (`id`) ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=293 DEFAULT CHARSET=utf8;

以下是 的一些值game：

id  home_team   away_team   winner  home_conference away_conference week    confidence

1   77  31  NULL    10  3   0   50    
2   59  96  NULL    7   12  0   50    
3   90  261 NULL    11  15  1   50

如您所见， 、 、 和 的值home_team是away_team不同home_conference表away_conference的外键。id当我查询所有 s 时，我想要一个查询，用返回值中其他表的实际值替换那些games。但我该怎么做呢？我已经尝试了这个的每一个变化：

SELECT * FROM game
LEFT JOIN team
ON game.home_team=team.id;

问题是这些是结果：

id  home_team   away_team   winner  home_conference away_conference week    confidence  id  name    conference_id
1   77  31  NULL    10  3   0   50  77  Colgate 10
2   59  96  NULL    7   12  0   50  59  Youngstown State    7
3   90  261 NULL    11  15  1   50  90  Morehead State  11

即使我可以选择team.name此查询的列，如果我将其扩展为

SELECT * FROM game
LEFT JOIN team
ON game.home_team=team.id AND game.away_team=team.id;

我没有得到任何结果。

我正在使用 MariaDB，顺便说一句。

标签： mysqlsqlmariadb

解决方案

SELECT * , 
       home.name as home_team,
       away.name as away_team
FROM game
LEFT JOIN team as home
  ON game.home_team= home.id 
LEFT JOIN team as away
  ON game.away_team= away.id

推荐阅读