employee
表包含所有员工,他们的经理也属于员工。每个员工都有一个 id,此外还有一列对应员工的经理的 id。
+----+-------+--------+-----------+ | id | name | salary | managerid | +----+-------+--------+-----------+ | 1 | joe | 70000 | 3 | | 2 | henry | 80000 | 4 | | 3 | sam | 60000 | null | | 4 | max | 90000 | null | +----+-------+--------+-----------+
给定employee
表,编写一个 sql 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,joe 是唯一一个收入超过他的经理的员工。
+----------+ | employee | +----------+ | joe | +----------+
方法一:查出两个表(都是employee表)
,分别as为a,b
select * from employee as a, employee as b where a.managerid = b.id and a.salary > b.salary
方法二:使用join
将两个表(同一个表)连接起来,使用on
指定条件(a.managerid = b.id)
select a.name as employee from employee as a join employee as b on a.managerid = b.id and a.salary > b.salary
摘自:
如对本文有疑问, 点击进行留言回复!!
网友评论