Oracle SQL多表关联查询：统计经理及员工审计完成情况

Got it, let’s tackle this problem step by step. First, I’ll make some reasonable assumptions about your table structures since you didn’t share the exact schema—feel free to tweak column names if they don’t match your actual tables:

• HR table: Stores employee-manager relationships, with columns like employee_id, manager_id, employee_name, manager_name
• REQUIRED_AUDITS table: Tracks audits that need to be performed, with columns like audit_id, employee_id (the employee assigned the audit), audit_category
• SCORE_ENTRY table: Logs completed audits, with columns like audit_id, audit_score, completed_timestamp

Here’s the SQL query that will calculate each manager’s required audit count, completed count, and completion rate:

SELECT
    mgr.manager_name,
    NVL(COUNT(DISTINCT ra.audit_id), 0) AS required_audit_count,
    NVL(COUNT(DISTINCT se.audit_id), 0) AS completed_audit_count,
    CASE
        WHEN NVL(COUNT(DISTINCT ra.audit_id), 0) = 0 THEN 0
        ELSE ROUND(
            (NVL(COUNT(DISTINCT se.audit_id), 0) / COUNT(DISTINCT ra.audit_id)) * 100,
            2
        )
    END AS completion_rate_percent
FROM
    HR emp
JOIN HR mgr ON emp.manager_id = mgr.employee_id
LEFT JOIN REQUIRED_AUDITS ra ON emp.employee_id = ra.employee_id
LEFT JOIN SCORE_ENTRY se ON ra.audit_id = se.audit_id
GROUP BY
    mgr.manager_id, mgr.manager_name -- Group by manager ID to avoid duplicates if names are same
ORDER BY
    completion_rate_percent DESC;

Let’s break down how this works: #

• Self-join on HR: Since managers are also employees in most HR schemas, we join the HR table to itself to link each employee to their manager, then group by manager details.
• LEFT JOIN usage: This ensures we include managers even if they have no required audits or no completed audits—no one gets excluded from the results.
• NVL function: Replaces NULL values (from no audits) with 0, so we don’t get messy calculation errors.
• COUNT(DISTINCT): Prevents duplicate counts if an audit shows up multiple times in your REQUIRED_AUDITS or SCORE_ENTRY tables.
• Completion rate logic: The CASE statement avoids division by zero (for managers with no required audits) and returns a clean percentage rounded to two decimal places.

Quick adjustments for your specific schema: #

• If your HR table already has a direct manager_name column for each employee (no need for self-join), simplify the FROM clause to start with HR h and group by h.manager_name.
• If your audit tables use a different key to link to employees (like user_id instead of employee_id), update the join conditions accordingly.

Let me know if you need to tweak this based on any unmentioned business rules or table specifics!

内容的提问来源于stack exchange，提问作者Richa