mysql - 如何将 UNION 与多查询一起使用

Question

我正在尝试根据他们的头衔来查找每个部门的员工人数，例如财务部门

Senior Staff = 12137
Staff = 5207
Manager = 2

我需要在 ONE 查询中对所有部门执行相同的操作，因为我必须在 Tableau 中将其可视化。这是我的 SQL 脚本。

set @dept_id='d002';
call new_procedure_total_emp_dept(@dept_id ,@result);

(select dn,staff_title, count(staff_title), (count(staff_title)/@result)*100  from 
 (
SELECT d.dept_name as dn, ti.title as staff_title FROM dept_emp de
    join titles ti using (emp_no)
    join departments d on d.dept_no = de.dept_no
    where de.dept_no = 'd002'  
    group by  de.emp_no
) as staff_no
 group by staff_title
 )
 
union

(
select dn,staff_title, count(staff_title), (count(staff_title)/17786)*100  from 
 (
SELECT d.dept_name as dn, ti.title as staff_title FROM dept_emp de
    join titles ti using (emp_no)
    join departments d on d.dept_no = de.dept_no
     where de.dept_no = 'd003'  
    group by  de.emp_no
) as staff_no
 group by staff_title
) 
 ;

标签： mysql