sql - 从没有活跃用户的列表中选择部门

Question

我有一个带有表的数据库'departments'，'branches'并且'staff'

部门 ：

id_department, name_department

分支 ：

id_branch, name_branch

职员 ：

id_staff, email, fk_branch, fk_department, status

我有一个部门及其各自分支机构的列表：

id_branch, id_department
1,2
1,3
2,5
3,2

我想过滤这个列表，只得到那些在公司中有活跃用户的部门。

我实际上得到了一个结果，但是我认为我使事情过于复杂，特别是在 GROUP BY 部分，我认为这可能没有必要。

WITH dept_managers_in_country AS
    (
        SELECT
            (
                SELECT 
                    id_staff 
                FROM dsv_global.staff 
                WHERE email = s.email
            ) as id_staff,
            s.email,
            dm.fk_branch,
            dm.fk_department
        FROM dsv_one_access.databases_access.department_module dm
        --Getting dept. manager fk_staff
        INNER JOIN dsv_one_access.databases_access.u_staff_modules usm
        ON usm.u_staff_modules = dm.fk_u_staff_modules
        --Getting dept. manager email
        INNER JOIN dsv_one_access.databases_access.staff s
        ON s.id_staff = usm.fk_staff

        WHERE s.fk_country = 6
    ),
    --departments current user has
    depts_in_charge AS
    (
        SELECT 
            fk_branch, 
            fk_department 
        FROM dept_managers_in_country
        WHERE id_staff = 960
        --won't work proced if user is not 
        --dept. manager and line below is removed   
        UNION SELECT 0,0
    )
SELECT 
        dic.fk_branch,
        dic.fk_department,
        d.name_department,
        d.complete_name_dept,
        --Choosing dept. manager in department
        (
            SELECT 
                id_staff AS id_supervisor,
                email 
            FROM dept_managers_in_country  
            WHERE  fk_branch = dic.fk_branch AND fk_department = dic.fk_department
            FOR JSON PATH
        ) as supervisors,
        'Department without active users' as comments

    FROM depts_in_charge dic

    INNER JOIN dsv_global.departments d
    ON dic.fk_department = d.id_department

    LEFT JOIN dsv_global.staff s
    on s.fk_branch = dic.fk_branch
    AND s.fk_department = dic.fk_department


    WHERE @level_view = 'department_manager'
    GROUP BY dic.fk_branch,
        dic.fk_department,
        d.name_department,
        d.complete_name_dept
    HAVING SUM(CASE WHEN s.status = 'ACTIVE' THEN 1 ELSE 0 END)  = 0

结果是：

fk_branch   fk_department   name_department complete_name_dept  supervisors comments
4   66  FIA Freight import[{"id_supervisor":960,"email":"[e-mail]"}] Department without active users

标签： sqlsql-serversubquery