题目:
Table: Employees
+-------------+----------+ | Column Name | Type | +-------------+----------+ | employee_id | int | | name | varchar | | reports_to | int | | age | int | +-------------+----------+ employee_id 是这个表的主键. 该表包含员工以及需要听取他们汇报的上级经理的ID的信息。 有些员工不需要向任何人汇报(reports_to 为空)。
对于此问题,我们将至少有一个其他员工需要向他汇报的员工,视为一个经理。
编写SQL查询需要听取汇报的所有经理的ID、名称、直接向该经理汇报的员工人数,以及这些员工的平均年龄,其中该平均年龄需要四舍五入到最接近的整数。
返回的结果集需要按照 employee_id
进行排序。
解题:
这题半推半就,几次尝试写出来了。
一开始我这样写的,
select employee_id,name,count(reports_to) as reports_count,
avg(age) as average_age from Employees group by employee_id,name having count(reports_to) > 0 order by employee_id
错误点在于:我这是找的员工每个人要汇报的经理数,而不是每个经理底下有多少人要向他汇报。
后来改正如下,需要用到自连接:
select e1.employee_id,e1.name,count(*) as reports_count,
round(avg(e2.age),0) as average_age from Employees e1,Employees e2 where
e1.employee_id = e2.reports_to group by e1.employee_id,e1.name order by employee_id
对此自连接,我的理解是,像是把一张表,拷贝了一份,然后拿笔去把符合条件的连线起来。我觉得这个比喻很绝,你可以细品一下。
这里要注意e1和e2的选择,也就是你要哪个表的数据。