如何跨表比较属性并返回多列？——查找同城市销售员与客户的SQL实现

Hey there, 要找出所属城市相同的销售员和客户并返回指定列，咱们可以用INNER JOIN来关联两张表，基于city字段做匹配就可以啦。

完整解决方案 #

首先，先确认你的表结构和数据已经正确创建（你提供的SQL语句我已经整理好了，方便直接测试）：

1. 创建并填充Salesman表

create table salesman 
(salesman_id int, 
name char(50), 
city char(50), 
commission float);

insert all 
into salesman(salesman_id,name,city,commission)values(5001,'James Hoong','New York',0.15) 
into salesman(salesman_id,name,city,commission)values(5002,'Nail Knite','Paris',0.13) 
into salesman(salesman_id,name,city,commission)values(5005,'Pit Alex','London',0.11) 
into salesman(salesman_id,name,city,commission)values(5006,'Mc Lyon','Paris',0.14) 
into salesman(salesman_id,name,city,commission)values(5007,'Paul Adam','Rome',0.13) 
into salesman(salesman_id,name,city,commission)values(5003,'Lauson Hen','San Jose',0.12) 
select * from dual;

2. 创建并填充Customer表

create table customer 
(customer_id int, 
name char(50), 
city char(50), 
commission int);

insert all 
into customer(customer_id,name,city,commission)values(3002,'Nick Rimando','New York',100) 
into customer(customer_id,name,city,commission)values(3007,'Brad Davis','New York',200) 
into customer(customer_id,name,city,commission)values(3005,'Graham Zusi','California',200) 
into customer(customer_id,name,city,commission)values(3008,'Julian Green','London',300) 
into customer(customer_id,name,city,commission)values(3004,'Fabian Johnson','Paris',300) 
into customer(customer_id,name,city,commission)values(3009,'Geoff Cameron','Berlin',100) 
into customer(customer_id,name,city,commission)values(3003,'Jozy Altidor','Moscow',200) 
into customer(customer_id,name,city,commission)values(3001,'Brad Guzan','London',null) 
select * from dual;

3. 查询同城市的销售员与客户

用INNER JOIN关联两张表，匹配条件是salesman.city = customer.city，然后选择需要的字段：

SELECT 
    s.name AS salesman_name,
    c.name AS customer_name,
    s.city AS city
FROM salesman s
INNER JOIN customer c ON s.city = c.city;

结果说明 #

执行上面的查询后，会返回所有城市匹配的组合：

• New York的James Hoong会和Nick Rimando、Brad Davis匹配
• Paris的Nail Knite、Mc Lyon会和Fabian Johnson匹配
• London的Pit Alex会和Julian Green、Brad Guzan匹配

这样就完美满足你要返回销售员姓名、客户姓名以及城市的需求啦～

内容的提问来源于stack exchange，提问作者soma