将同一表中的两列相交的SQL查询:

Question

我有一个employee表，看起来像这样:

column_name   | column_id |
employee_id   |     1     |
first_name    |     2     |
last_name     |     3     |     
department_id |     11    |

Employees表还有一个引用Departments表主键的外键:

column name   | column id |    
department_id |     1     |

我的目标是使用INTERSECT来显示所有既用作姓又用作名的雇员名。(例如:如果'Lee'是姓和名，我想特别显示他的名字)。

我正在显示部门表，因为我不确定是否需要JOIN来获得我想要的结果。

首先，我得到了所有人的名字和姓氏的完整列表:

SELECT e.first_name, e.last_name
FROM Employees e
INTERSECT
SELECT e2.first_name, e2.last_name
FROM Employees e2;

但我只想要出现在first_name和last_name列中的名称。

我已经试过了:

SELECT e.first_name, e.last_name
FROM Employees e
WHERE e.first_name = e.last_name
INTERSECT
SELECT e2.first_name, e2.last_name
FROM Employees e2
WHERE e2.first_name = e2.last_name;

没有行被选中。

我也尝试使用JOIN:

SELECT e.first_name, e.last_name
FROM Employees e
JOIN Departments d ON d.department_id = e.department_id
INTERSECT
SELECT e2.first_name, e2.last_name
FROM Employees e2
JOIN Departments d2 ON d2.department_id = e2.department_id;

And this select ALL the row .

我也想知道是否有一种方法来INTERSECT这些列，而不使用WHERE子句?

Answer 1

它有助于逐步构建您的查询。

我们将首先获得一个同时包含姓和名的名字列表。这就是你的INTERSECT的由来。

SELECT e.first_name
FROM   employees e
INTERSECT
SELECT e.last_name
FROM   employees e;

现在，你有了那个列表。接下来，您希望显示姓名在该列表中的任何员工的详细信息。可以把它想象成"在哪里(某些标准)显示员工详细信息"。我们将从员工详细信息查询开始，然后添加条件，其中将包括上面的INTERSECT查询。

因此，员工详细信息的基本查询将是:

SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE ???  -- we need to plug the first query in here somehow.

然后，把它们放在一起…

SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE  (
e.first_name IN 
( SELECT e.first_name
FROM   employees e
INTERSECT
SELECT e.last_name
FROM   employees e )
OR       e.last_name IN 
( SELECT e.first_name
FROM   employees e
INTERSECT
SELECT e.last_name
FROM   employees e )
)

注意，我们已经重复了INTERSECT部分，重复是不好的，所以我们将使其成为一个通用的表表达式(想想"WITHclause")

WITH   names (name) AS
( SELECT e.first_name name
FROM   employees e
INTERSECT
SELECT e.last_name name
FROM   employees e )
SELECT e.first_name, e.last_name, d.department_id
FROM   employees e
INNER JOIN departments d ON d.department_id = e.department_id
WHERE  (
e.first_name IN ( SELECT name FROM names )
OR       e.last_name IN ( SELECT name FROM names )
)

这样就可以了。

Answer 2

这样如何:

-- get intersection of first and last name sets
select distinct first_name as first_and_last_name from employees
intersect
select distinct last_name as first_and_last_name from employees;

或:

-- get first names that are also last names
select distinct first_name from employees
where first_name in (select distinct last_name from employees);

没有理由担心department表，除非您想在结果集中包含该表中的数据或使用它来过滤结果集。

Answer 3

With EXIST:

SELECT first_name FROM employees e WHERE EXIST (SELECT * FROM employees ee WHERE ee.last_name = e.first_name)