Oracle SQL-查询以从多个表中计算值

Question

我试图通过连接多个表从sql查询中获得所需的结果。由于知识有限，因此寻求大家的帮助。

我正在尝试获取每个经理和员工的审核详细信息。我有3张桌子：

1. HR
2. REQUIRED_AUDITS
3. SCORE_ENTRY

以下是 HR 表的示例数据：

+----+---------------+------------------+
| id | manager_email |        VP        |
+----+---------------+------------------+
|  1 | [e-mail]  | [e-mail]     |
|  2 | [e-mail] | [e-mail] |
|  3 | [e-mail] | [e-mail]    |
|  4 | [e-mail] | [e-mail]    |
|  5 | [e-mail]  | [e-mail]      |
+----+---------------+------------------+

以下是 REQUIRED_AUDITS 表的示例数据：

+----+---------------+----------------+-----------------+----------------+
| ID | manager_email | employee_email | audits_required | audit_eligible |
+----+---------------+----------------+-----------------+----------------+
|  1 | [e-mail]  | [e-mail]   |               5 | Y              |
|  2 | [e-mail] | [e-mail] |               2 | Y              |
|  3 | [e-mail] | [e-mail]  |               7 | Y              |
|  4 | [e-mail]  | [e-mail] |               5 | Y              |
|  5 | [e-mail] | [e-mail]  |              25 | N              |
+----+---------------+----------------+-----------------+----------------+

以下是 SCORE_ENTRY 表的示例数据：

+----+---------------+----------------+-------+
| ID | manager_email | employee_email | Score |
+----+---------------+----------------+-------+
|  1 | [e-mail] | [e-mail] | 85.04 |
|  2 | [e-mail] | [e-mail]  |   100 |
|  3 | [e-mail]  | [e-mail] | 80.50 |
+----+---------------+----------------+-------+

因此，现在我想显示每个经理需要进行的审核数量，完成的数量以及完成的百分比。

+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| Manager(list from "HR" table) | Total Audits Required(Sum of audits required from "REQUIRED_AUDITS table") | Audits Performed(from "SCORE_ENTRY" table) | Percentage Completion |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| [e-mail]                  |                                                                         10 |                                          1 | 10%                   |
| [e-mail]                 |                                                                          9 |                                          2 | 22.22%                |
| [e-mail]                 |                                                                          - |                                          - | -                     |
| [e-mail]                 |                                                                          - |                                          - | -                     |
| [e-mail]                  |                                                                          - |                                          - | -                     |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+

计算方法如下：
1.要计算审核数，即从 REQUIRED_AUDITS 表中：
考虑经理[e-mail]：
所需审核总数：7 + 3，因为只有两名员工符合资格
。2.已完成的审核总数：
audits_required（来自REQUIRED AUDITS表）-审核完成（来自SCORE_ENTRY表）

如前所述，我对sql的了解有限，这对我来说真的很复杂，因此在Stack Overflow中有所涉足。

感谢任何帮助。

UPDATE 为了进一步简化过程，我编写了不包含HR表的离散查询，并且在组合以下查询时需要帮助。

查询1：我称其为 “ DENOMINATOR” ：

SELECT required_audits.manager_email, 
       SUM(audits_required) AS "TOTAL_AUDITS_REQUIRED" 
FROM   required_audits 
WHERE  Upper(required_audits.audit_eligible) = Upper('Y') 
GROUP  BY required_audits.manager_email 
ORDER  BY "total_audits_required" DESC

+-------------------------------+-----------------------+
| required_audits.manager_email | TOTAL_AUDITS_REQUIRED |
+-------------------------------+-----------------------+
| [e-mail]                  |                    10 |
| [e-mail]                 |                     9 |
+-------------------------------+-----------------------+

查询2：我称其为 “ NUMERATOR” ：

SELECT score_entry.manager_email, 
       Count(id) 
FROM   score_entry 
GROUP  BY score_entry.manager_email

+---------------------------+-----------+
| score_entry.manager_email | Count(id) |
+---------------------------+-----------+
| [e-mail]              |         1 |
| [e-mail]             |         2 |
+---------------------------+-----------+

在最终结果中，我只需要NUMERATOR / DENOMINATOR * 100％。我对join和在where子句中应用条件感到困惑。

+---------------+-----------------------+------------------+-----------------------+
|    Manager    | Total Audits Required | Audits Performed | Percentage Completion |
+---------------+-----------------------+------------------+-----------------------+
| [e-mail]  |                    10 |                1 | 10%                   |
| [e-mail] |                     9 |                2 | 22.22%                |
+---------------+-----------------------+------------------+-----------------------+

在结果表上，仅应显示符合审计条件的数字。这是哪里出了问题的另一个原因。

提前致谢。

谢谢

Answer 1

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

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_Performed

SELECT h.manager_email manager,
  a.Total_audits,
  s.Audits_Performed,
  100 * s.Audits_Performed / a.Total_audits percentage_complete
FROM HR h
LEFT OUTER JOIN aud a
ON h.manager_email = a.manager_email
LEFT OUTER JOIN scores s
ON h.manager_email = s.manager_email
ORDER BY 2 DESC NULLS LAST

结果 ：

|       MANAGER | TOTAL_AUDITS | AUDITS_PERFORMED | PERCENTAGE_COMPLETE |
|---------------|--------------|------------------|---------------------|
|  [e-mail] |           10 |                1 |                  10 |
| [e-mail] |            9 |                2 |   22.22222222222222 |
| [e-mail] |       (null) |           (null) |              (null) |
|  [e-mail] |       (null) |           (null) |              (null) |
| [e-mail] |       (null) |           (null) |              (null) |