使用WITH AS提高性能简化嵌套SQL

CTE 使用案例:

with cte_value as (
    select dept_id,month,value,count(1) isum
    from eom_coefficient_submit 
    where dept_id = 500
    and month = '2022-04'
    GROUP BY dept_id,month,value
    ),
    cte_total as (
        select gr.dept_id,count(1) total
        from eom_message_employee_dlms em
        left join eom_group_message_dlms gr
        on em.department_id = gr.group_id 
        where gr.dept_id = 500
        and user_status = 1
        GROUP BY gr.dept_id
    ),
    cte_submit as (
        select dept_id,count(1) sum
        from eom_coefficient_submit 
        where dept_id = 500
        and month = '2022-04'
        GROUP BY dept_id
    )
select a.dept_id,a.month,tol.total,su.sum,a.value,a.isum
from cte_value a
left join cte_total tol on a.dept_id = tol.dept_id
left join cte_submit su on a.dept_id = su.dept_id
order by a.value