--首先求出每个员工的收件总量 with base0 as ( select e_id,count(b_id) as sl from bill_info where year(data)=2019 and month(data)=8 group by e_id ), --然后求出每个大区的员工及其收件量 base1 as ( select a.e_id,a.e_name,a.area,b.sl from employee_info left join base0 b on a.e_id = b.e_id group by a.area ) --对数量排序并选取10-20名信息 select e_name,e_id,sl from (select e_name,e_id,sl,rank() over (partition by area order by sl desc) as rank from base1 where 10<=rank<=20)a