mysql - 多输出MySQL

Question

天哪，我今天开始使用 MySQL，但我在臭名昭著的 3 表代码方面遇到了麻烦。一切看起来都不错，除了每个输出我得到 4 个。我想知道是否有人可以查看我的代码并告诉我为什么我会得到 4 个相同的输出。

代码如下:

  mysql -u root -p
show databases;
drop database if exists Game_Stuff;
create database Game_Stuff;
Use Game_Stuff;

create table mygames(GameID varchar(8) not null primary key, 
Title varchar(20) not null,
Genre varchar(10) not null,
Gamedescription varchar(20) not null, 
Gamerating varchar(4) not null, 
Releasedate varchar(10) not null,
Devid varchar (8) not null,
CountryID varchar(3) not null);

insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('P1', 'Pacman', 'retro', 'map walk around', '80%', '12/12/83', 'Namco', 'Jap');

insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('SF1', 'Street Fighter', 'Old MMA', 'Button masher', '92%', '22/7/88', 'Capcom', 'Jap');

insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('Son1', 'Sonic', 'Platform', 'Reach the end without dying', '95%', '22/7/88', 'Sonic Team', 'Jap');

insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('Mar1', 'Super Mario', 'Platform', 'Rescue Peach', '100%', '13/9/85', 'nintendo', 'Jap');

insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('Berz', 'Berzerk', 'Atari', 'Run and Gun', '100%', '1/1/80', 'Stern', 'USA');


insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('Rnr1', 'runner', 'Atari', 'Run and Gun', '100%', '1/1/80', 'Stern', 'USA');


insert into mygames(GameID, Title, Genre, Gamedescription, Gamerating, Releasedate, devid, CountryID) values
('Rnr12', 'runn4536er', 'Atari', 'Run and Gun', '100%', '1/1/80', 'Stern', 'USA');

create table developers(devid varchar(20) not null,
devcity varchar(25) not null,
devcountry varchar(20) not null,
devgames varchar(100) not null,
devest varchar (10) not null,
CountryID varchar(3) not null);

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('Namco', 'Tokyo', 'Japan', '1/5/55', 'Jap');

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('Capcom', 'Osaka', 'Japan', '11/5/83', 'Jap');

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('Sonic Team', 'Tokyo', 'Japan', '1/1/1990', 'Jap');

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('nintendo', 'Kyoto', 'Japan', '23/8/89', 'Jap');

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('Stern', 'Philadelphia', 'America', '--/--/1946', 'USA');

insert into developers(devid, devcity, devcountry, devest, CountryID) values
('nintendo1', 'Kyoto1', 'Japan', '23/8/89', 'Jap');


create table Countries1(CountryID varchar(3) not null primary key,
Name varchar(25) not null,
Continent varchar(20) not null);

insert into Countries1(CountryID, Name, Continent) values
('Jap', 'Japan', 'Asia');

insert into Countries1(CountryID, Name, Continent) values
('USA', 'America', 'North America');

SELECT Title, Name 
FROM Countries1
inner JOIN developers
ON Countries1.CountryID = developers.CountryID 
inner JOIN mygames
ON developers.CountryID = mygames.CountryID
where Countries1.CountryID = 'Jap';

Answer 1

如果您只追求头衔和名称，为什么还要让开发人员参与进来？

SELECT Title, Name 
FROM mygames m
LEFT JOIN Countries1 c
ON c.CountryID = m.CountryID 
WHERE m.CountryID = 'Jap';