1、员工关系表和职称表内连接,查询在职人员的部门,职称和职称的数量,并排序
select b.dept_no,title,count(title) as count from dept_emp b join titles as c on b.emp_no=c.emp_no where c.to_date='9999-01-01' group by b.dept_no,title order by b.dept_no,title2、部门表和1表内连接,查询结果
select a.dept_no,a.dept_name,d.title,d.count from departments a join (select b.dept_no,title,count(title) as count from dept_emp b join titles as c on b.emp_no=c.emp_no where c.to_date='9999-01-01' group by b.dept_no,title order by b.dept_no,title ) as d on a.dept_no=d.dept_no;