SQL:为什么这两个 sql 查询结果不同，谢谢各位赐教。

This topic created in 988 days ago, the information mentioned may be changed or developed.

第一个查询：
SELECT d.name Department, e.name Employee, e.salary Salary
FROM Employee e LEFT JOIN department d
ON e.departmentId = d.id
WHERE (d.id,e.salary) IN
(
SELECT departmentId ,max(salary)
FROM Employee
GROUP BY departmentId;
);

第二个查询：
SELECT d.name Department, e.name Employee, e.salary Salary
FROM Employee e LEFT JOIN department d
ON e.departmentId = d.id
WHERE (d.id,e.salary) IN
(
SELECT e.departmentId ,max(e.salary)
FROM Employee
GROUP BY e.departmentId;
);

Supplement 1 · Aug 28, 2023

184. Department Highest Salary
Medium
1.8K
180
Companies
SQL Schema
Pandas Schema
Table: Employee

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

Table: Department

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.

Write a solution to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+

Supplement 2 · Aug 28, 2023

184. Department Highest Salary

Table: Employee

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference columns) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department.

Table: Department

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL.
Each row of this table indicates the ID of a department and its name.

Write a solution to find employees who have the highest salary in each of the departments.

Return the result table in any order.

The result format is in the following example.

Example 1:

Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
| IT | Max | 90000 |
+------------+----------+--------+

Supplement 3 · Aug 28, 2023

感谢各位的帮助，虽然我还不理解，再次感谢

3 replies • 2023-08-29 04:03:09 +08:00

F281M6Dh8DXpD1g2

Aug 28, 2023 via iPhone

执行计划看了么
第二个 in 里面的 e 去掉就一样了

dw2693734d

Aug 28, 2023

两个 SQL 查询之间的主要区别在于子查询中的 SELECT 语句：

第一个查询：SELECT departmentId ,max(salary) FROM Employee GROUP BY departmentId;
第二个查询：SELECT e.departmentId ,max(e.salary) FROM Employee GROUP BY e.departmentId;
实际上，从逻辑上看，这两个子查询是等价的。它们都是从 Employee 表中选择 departmentId 和该部门中的最高工资（ max(salary)），并按 departmentId 进行分组。

因此，如果两个查询返回的结果不同，那可能是由于其他因素引起的，比如数据的不同或数据库引擎的特定行为。但根据这两个 SQL 语句本身，它们应该返回相同的结果。

c6h6benzene

Aug 29, 2023

是结果不同，还是结果的顺序不同？你两个 except 一下看看能不能找出区别。