mysql - 在另一个 select 语句中选择两次

Question

我有以下表架构和数据

CREATE TABLE Customer
(
    First_Name char(50),
    Last_Name char(50),
    Address char(50),
    Age  int,
    City char(50),
    Birth_Country char(25),
    Live_Country char(25),
    Birth_Date datetime
);

insert into Customer (first_name, last_name, address, age, city, Birth_Country, Live_Country, birth_date) values('John', 'Doe', 'Xi-Fu Road', 29,  'Tokyo', 'Japan', 'Japan','1988-11-19') ;
insert into Customer (first_name, last_name, address, age, city, Birth_Country, Live_Country, birth_date) values('Mary', 'Fai', 'Xi-Fu Road', 30,'Tokyo', 'Japan', 'Japan','1987-04-14') ;
insert into Customer (first_name, last_name, address, age, city, Birth_Country, Live_Country, birth_date) values('Tim', 'Potter', 'Gan-xuiu Road', 30, 'NewYork', 'US','Germany', '1987-04-11') ;
insert into Customer (first_name, last_name, address, age, city, Birth_Country, Live_Country, birth_date) values('Lala', 'Hua','Gong-Fu Road',  31, 'Bei-Jing', 'PRC', 'US','1986-12-11') ;
insert into Customer (first_name, last_name, address, age, city, Birth_Country, Live_Country, birth_date) values('Marcia', 'Changhua', 'To-Fu Road', 41 ,'London', 'French', 'Japan','1976-01-04') ;

CREATE TABLE Country_Info
(
    ID int,
    Country char(50)        
);


insert into Country_Info(ID, Country) values(0, 'Japan') ;
insert into Country_Info(ID, Country) values(1, 'US') ;
insert into Country_Info(ID, Country) values(2, 'Germany') ;
insert into Country_Info(ID, Country) values(3, 'PRC') ;
insert into Country_Info(ID, Country) values(4, 'French') ;

我想知道客户的 live_country 和birth_country ID。

我使用以下查询

select ID from Country_Info, (select Birth_Country as birth, Live_Country as live from Customer) as tmp_table where 
birth = Country_Info.Country ;

该查询仅获取客户的birth_country ID；

但是我想获取birth_country和live_country的ID，并以两列显示。

是否可以一次查询一次？

Answer 1

现在使用您的数据，尝试此操作

SELECT  C.first_name,C.last_name,
        Birth.ID as Birth_Country_ID,
        Live.ID as Live_Country_ID
FROM Customer as C
INNER JOIN Country_Info as Birth ON C.Birth_Country = Birth.Country
INNER JOIN Country_Info as Live ON C.Live_Country = Live.Country

http://sqlfiddle.com/#!9/c4678e/2然后添加 WHERE C.first_name = 'somename' 来查找特定客户。

但我建议您将 auto_increment id 添加到您的客户表中。并且不要将国家/地区名称保存在客户表中，而是保存国家/地区的 ID。