Oracle SQL

您可以使用多个公用表表达式，分别计算每个表并将它们连接在一起-只是为了您了解发生了什么。

SQL小提琴

查询 ：

WITH aud(manager_email,Total_audits) AS  (SELECt manager_email,    SUM (    CASE      WHEN audit_eligible = 'Y'      THEN audits_required    END )  FROM REQUIRED_AUDITS  GROUP BY manager_email  ),  --Total_audits  scores(manager_email,Audits_Performed) AS  (SELECt manager_email,    COUNT ( ID )  FROM SCORE_ENTRY s  GROUP BY manager_email  )  --Audits_PerformedSELECt h.manager_email manager,  a.Total_audits,  s.Audits_Performed,  100 * s.Audits_Performed / a.Total_audits percentage_completeFROM HR hLEFT OUTER JOIN aud aON h.manager_email = a.manager_emailLEFT OUTER JOIN scores sON h.manager_email = s.manager_emailORDER BY 2 DESC NULLS LAST

结果 ：

|       MANAGER | TOTAL_AUDITS | AUDITS_PERFORMED | PERCENTAGE_COMPLETE ||---------------|--------------|------------------|---------------------||  john@com.com |10 |     1 |       10 || linda@com.com | 9 |     2 |   22.22222222222222 || smith@com.com |       (null) |(null) |   (null) ||  jess@com.com |       (null) |(null) |   (null) || maria@com.com |       (null) |(null) |   (null) |