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

小编典典

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

sql

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

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

1. HR
2. REQUIRED_AUDITS
3. SCORE_ENTRY

以下是 HR 表的示例数据：

+----+---------------+------------------+
| id | manager_email |        VP        |
+----+---------------+------------------+
|  1 | john@com.com  | jake@com.com     |
|  2 | smith@com.com | kathleen@com.com |
|  3 | maria@com.com | james@com.com    |
|  4 | linda@com.com | david@com.com    |
|  5 | jess@com.com  | kim@com.com      |
+----+---------------+------------------+

以下是 REQUIRED_AUDITS 表的示例数据：

+----+---------------+----------------+-----------------+----------------+
| ID | manager_email | employee_email | audits_required | audit_eligible |
+----+---------------+----------------+-----------------+----------------+
|  1 | john@com.com  | brad@com.com   |               5 | Y              |
|  2 | linda@com.com | gloria@com.com |               2 | Y              |
|  3 | linda@com.com | susan@com.com  |               7 | Y              |
|  4 | john@com.com  | carmen@com.com |               5 | Y              |
|  5 | linda@com.com | aaron@com.com  |              25 | N              |
+----+---------------+----------------+-----------------+----------------+

以下是 SCORE_ENTRY 表的示例数据：

+----+---------------+----------------+-------+
| ID | manager_email | employee_email | Score |
+----+---------------+----------------+-------+
|  1 | linda@com.com | gloria@com.com | 85.04 |
|  2 | linda@com.com | susan@com.com  |   100 |
|  3 | john@com.com  | carmen@com.com | 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 |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| john@com.com                  |                                                                         10 |                                          1 | 10%                   |
| linda@com.com                 |                                                                          9 |                                          2 | 22.22%                |
| smith@com.com                 |                                                                          - |                                          - | -                     |
| maria@com.com                 |                                                                          - |                                          - | -                     |
| jess@com.com                  |                                                                          - |                                          - | -                     |
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+

计算方法如下：
1.要计算审核数，即从 REQUIRED_AUDITS 表中：
考虑经理linda@com.com：
所需审核总数：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 |
+-------------------------------+-----------------------+
| john@com.com                  |                    10 |
| linda@com.com                 |                     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) |
+---------------------------+-----------+
| john@com.com              |         1 |
| linda@com.com             |         2 |
+---------------------------+-----------+

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

+---------------+-----------------------+------------------+-----------------------+
|    Manager    | Total Audits Required | Audits Performed | Percentage Completion |
+---------------+-----------------------+------------------+-----------------------+
| john@com.com  |                    10 |                1 | 10%                   |
| linda@com.com |                     9 |                2 | 22.22%                |
+---------------+-----------------------+------------------+-----------------------+

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

提前致谢。

谢谢

阅读 172

2021-05-16

共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 |
|---------------|--------------|------------------|---------------------|
|  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) |

2021-05-16