h*******m
发帖数: 39 | 1 一道面试题，第一个table employee,第二个table department. 现在要用sql找出每一
个department里工资最高的人。答案在后面可我看不懂那个e1.name as employee是啥
意思？e2又是从哪冒出来的? 谢谢啦
employee
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
department
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
select Department.Name as Department, e1.Name as Employee, Salary
from Employee e1, Department
where e1.DepartmentId = Department.Id
and
Salary >= ALL (select Salary from Employee e2 where e2.DepartmentId = e1.
DepartmentId); | x****e
发帖数: 1773 | 2 Check correlated subqueries.
【在 h*******m 的大作中提到】
: 一道面试题，第一个table employee,第二个table department. 现在要用sql找出每一
: 个department里工资最高的人。答案在后面可我看不懂那个e1.name as employee是啥
: 意思？e2又是从哪冒出来的? 谢谢啦
: employee
: +----+-------+--------+--------------+
: | Id | Name | Salary | DepartmentId |
: +----+-------+--------+--------------+
: | 1 | Joe | 70000 | 1 |
: | 2 | Henry | 80000 | 2 |
: | 3 | Sam | 60000 | 2 |
| h*******m
发帖数: 39 | | j********5
发帖数: 281 | 4 1,from Employee e1---here rename table employee in first query as e1 table,
2,ALL (select Salary from Employee e2---here rename the same table employee
in 2nd query as e2 table,
usually in this way, you can distinguish same table(employee) from different
queries, in order to prevent confusion. | j********5
发帖数: 281 | 5 1,from Employee e1---here rename table employee in first query as e1 table,
2,ALL (select Salary from Employee e2---here rename the same table employee
in 2nd query as e2 table,
usually in this way, you can distinguish same table(employee) from different
queries, in order to prevent confusion. |
|
