ORACLE：多列子查询

ORACLE: multiple column subqueries

提问by Pepperwork

This is from Oracle Database 10g: SQL Fundamentals. Original Oracle textbook. Francly speaking, this all is actual so far.

这是来自 Oracle 数据库 10g：SQL 基础知识。原版 Oracle 教科书。坦率地说，到目前为止，这一切都是真实的。

There is a task which troubles me:

有一个任务困扰着我：

Display the last name, department name, and salary of any employee whose salary and commission match the salary and commission of many employee located in location ID 1700.

显示工资和佣金与位置 ID 1700 的许多员工的工资和佣金匹配的任何员工的姓氏、部门名称和工资。

The topic to be learned is multiple column subqueries. This mean that we can't deviate from the offered model which is:

要学习的主题是多列子查询。这意味着我们不能偏离所提供的模型：

SELECT column, column, ...
FROM table
WHERE (column, column, ...) IN
(SELECT column, column, ...
FROM table
WHERE condition);

Well, my database:

好吧，我的数据库：

describe employees
Name           Null     Type         
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
EMAIL          NOT NULL VARCHAR2(25) 
PHONE_NUMBER            VARCHAR2(20) 
HIRE_DATE      NOT NULL DATE         
JOB_ID         NOT NULL VARCHAR2(10) 
SALARY                  NUMBER(8,2)  
COMMISSION_PCT          NUMBER(2,2)  
MANAGER_ID              NUMBER(6)    
DEPARTMENT_ID           NUMBER(4)    

describe departments
Name            Null     Type         
--------------- -------- ------------ 
DEPARTMENT_ID   NOT NULL NUMBER(4)    
DEPARTMENT_NAME NOT NULL VARCHAR2(30) 
MANAGER_ID               NUMBER(6)    
LOCATION_ID              NUMBER(4)  

There is a solution:

有一个解决方案：

select
  e.last_name,
  d.department_name, 
  e.salary
from employees e, departments d
where e.department_id = d.department_id
and (salary, nvl(commission_pct, 0)) in
  (select salary, nvl(commission_pct, 0)
   from employees e, departments d
   where e.department_id = d.department_id
   and d.location_id = 1700);

SELECTED 36 ROWS.

选择了 36 行。

Well, I started checking. It seems that employees from location 1700 duplicated against themselves. Look: we take the whole list of employees and then juxtapose it with employees from location 1700. Of course, there will be duplicates.

嗯，我开始检查。似乎来自位置 1700 的员工复制了自己。看：我们把整个员工列表和位置 1700 的员工并列在一起。当然，会有重复的。

I have prepared an excel file with some data (all employees, employees from location 1700, the result of my own desired result etc.). In the file it is more picturesque, with examples and so on. Please, have a look.

我准备了一个包含一些数据的 excel 文件（所有员工，来自位置 1700 的员工，我自己想要的结果等）。在文件中，它更加风景如画，并附有示例等。请看一看。

This is the file: https://skydrive.live.com/redir?resid=8CDFFBA921B002FE!150&authkey=!ADMRAE466BIunQM

这是文件：https: //skydrive.live.com/redir?resid=8CDFFBA921B002FE!150&authkey=!ADMRAE466BIunQM

Well, what I would like to do is to control that no employee is compared with themselves. This was my variant bevore I checked the solution.

嗯，我想要做的是控制没有员工与自己进行比较。这是我检查解决方案之前的变体。

select lnme, dpt, slry 
from
(
  select       
      employee_id eid,
      last_name lnme, 
      salary slry, 
      nvl(commission_pct,0) cpct,
      d.department_name dpt,
      location_id
  from employees e
  left join departments d
  on e.department_id = d.department_id
)
where (slry, cpct) in
(select 
    employee_id ide,
    salary slry, 
    nvl(commission_pct,0) cpct    
from employees e
join departments d
on e.department_id = d.department_id and location_id = 1700)
and ide <> eid

I wanted to make sure that no employee is compared with the same employee. But I failed.

我想确保没有员工与同一位员工进行比较。但我失败了。

Could you comment on all this situation: 1. Whether I'm completely wrong and the solution of Oracle is perfect. 2. If Oracle is wrong, could you help me with this code?

您能否评论一下所有这些情况： 1.我是否完全错了，Oracle的解决方案是否完美。2.如果Oracle出错了，你能帮我写下这段代码吗？

Thank you in advance.

先感谢您。

回答by APC

The question asks you to identify any employeewhose salary and commission matches any combination of salary and salary of employees in Department 1700. The question doesn't say to exclude employees from that department. So it is quite correct for them to appear in the result set.

这个问题问你，以确定任何员工，其工资和佣金的问题不说，从该部门员工排除薪水和部门1700名员工的工资的任意组合相匹配。所以它们出现在结果集中是非常正确的。

"One employee can't match themselves."

“一名员工无法匹配自己。”

You're not matching employees, you're matching salaries. It just so happens that the list of target salaries is generated from a sub-query on department 1700: it could just as easily be a list of numbers from a CSV file.

您不是在匹配员工，而是在匹配工资。碰巧目标工资列表是从部门 1700 的子查询中生成的：它可以很容易地是来自 CSV 文件的数字列表。

---

---

Let's have a thought experiment. There are three employees in department 1700:

让我们做一个思想实验。部门1700有3名员工：

name       | salary  | comm
-----------+---------+-----
CHIP       |  10,100 |    0  
RUNCITER   |  12,200 |    0  
CONLEY     |  10,500 |    0  

Nobody else in the company earns above 10K.

公司中没有其他人的收入超过 10K。

So what is the answer to the question: how many employee have a salary and commission which match the salary and commission of any employee located in department 1700?

那么这个问题的答案是什么：有多少员工的工资和佣金与位于部门 1700 的任何员工的工资和佣金相匹配？

1. Three
2. Zero

1. 三
2. 零

Then Joe gets a modest raise and the salaries look like this:

然后乔得到了适度的加薪，工资是这样的：

name       | salary  | comm
-----------+---------+-----
CHIP       |  10,500 |    0  
RUNCITER   |  12,200 |    0  
CONLEY     |  10,500 |    0  

So what is the answer to the same question now?

那么现在同一个问题的答案是什么？

1. Still three
2. Two

1. 还是三个
2. 二

The answer is 'three' in both cases.

在这两种情况下，答案都是“三”。

Why?

为什么？

Because SQL is a set-based programming language, and the set of all employees who earn the exact salary and commission as employees in department 1700 must include all employees who work in department 1700.

因为 SQL 是一种基于集合的编程语言，所有在部门 1700 中获得确切工资和佣金的员工的集合必须包括所有在部门 1700 工作的员工。

There is a different set, which is the set of all employees who earn the exact salary and commission as employees in department 1700 who don't themselves work in department 1700. But that is not the set the SQL Fundamentals quiz is looking for.

有一个不同的集合，它是所有在部门 1700 中获得确切薪水和佣金但自己不在部门 1700 工作的员工的集合。但这不是 SQL Fundamentals 测验要寻找的集合。