sql - 如何使用两个表进行 SQL 查询

Question

我有这两个表:

Users - 我们通过用户 ID 获取县和城市 ID

Names_list - 所在城市或县名

Users

id | county | city
---+--------+-----
1  | 123    | 456

Names_list

id  | name
----+-----
123 | ABC
456 | DEF

询问:

SELECT Users.id as id,  
  Users.county as county, 
  Users.city as city,  
  Names_list.name as name1 
  Names_list.name as name2 
FROM `Users` 
LEFT JOIN Names_list ON (Names_list.id = Users.city) 
LEFT JOIN Names_list ON (Names_list.id = Users.county) 
WHERE Users.id = '1'

需要返回此输出:

id | county | name1 | city | name2
---+-------+--------+------+------
1  | 123    | ABC   | 456  | DEF

但是，输出当然是错误的。 Names_list.id 重复。需要另一种解决方案才能在一行中返回。

Answer 1

你只需要别名:

SELECT u.id as id, u.county as county, u.city as city,  
       nc.name as name1  nco.name as name2 
FROM `Users` u LEFT JOIN
     Names_list nc
     ON nc.id = u.city LEFT JOIN 
     Names_list nco
    ON nco.id = u.county
WHERE u.id = 1;  -- I'm guessing this is a number so am removing the single quotes.