时间戳

首页 > 解决方案 > 如何比较两个不同表中的两个属性并返回多个列

oracle - 如何比较两个不同表中的两个属性并返回多个列

问题描述

从下表中我想找到属于同一城市的销售人员和客户，最后返回销售人员、客户名称和城市

这是我创建的表

推销员表

create table salesman
  2  (salesman_id int,
  3  name char(50),
  4  city char(50),
  5  commission float);

在 salesman 表中插入的行

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

客户表

create table customer
  2  (customer_id int,
  3  name char(50),
  4  city char(50),
  5  commission int);

客户表中的行

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

标签： oracle

解决方案

听起来你想要一个非常简单的INNER JOIN.

select s.name as salesperson, c.name as customer, s.city
from salesman s
join customer c
  on c.city = s.city

推荐阅读