Oracle SQL-查询以从多个表中计算值 [英] Oracle SQL - Query to calculate values from multiple tables
问题描述
我试图通过连接多个表从sql查询中获得所需的结果.由于知识有限，因此寻求大家的帮助.
Am trying to get desired results from sql query by joining multiple tables. Since am having limited knowledge, seeking help from you all.
正在尝试获取每个经理和员工的审核详细信息.我有3张桌子:
Am trying to get audit details per manager and employees. I have 3 tables:
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 |
+----+---------------+----------------+-------+
所以现在我想显示每个经理需要进行的审核数量，完成的数量以及完成的百分比.
So now I want to show number of audits required for each manager and how many completed and also percentage of completion.
+-------------------------------+----------------------------------------------------------------------------+--------------------------------------------+-----------------------+
| 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表)
Calculations are as per below:
1. To calculate number of audits i.e., from REQUIRED_AUDITS table:
Considering manager linda@com.com:
Total audits required: 7+3 because only two employees are eligible
2. Total audits completed:
audits_required (from REQUIRED AUDITS table) - audits completed (from SCORE_ENTRY table)
如前所述，我对sql的了解有限，这对我来说看起来真的很复杂，因此可以扩展到Stack Overflow.
As said am having limited knowledge on sql and this look really complex for me and hence reaching out in Stack Overflow.
感谢任何帮助.
更新 为了进一步简化流程，我编写了不包含HR表的离散查询，并且在组合以下查询时需要帮助.
UPDATE To further simplify the process, I wrote discrete queries excluding HR table and need help in combining below queries.
查询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％.在连接中感到困惑，并在where子句中应用条件.
In the final or result, all I need is NUMERATOR**/**DENOMINATOR * 100 in percent. Am getting confused in joins and applying conditions in where clause.
+---------------+-----------------------+------------------+-----------------------+
| Manager | Total Audits Required | Audits Performed | Percentage Completion |
+---------------+-----------------------+------------------+-----------------------+
| john@com.com | 10 | 1 | 10% |
| linda@com.com | 9 | 2 | 22.22% |
+---------------+-----------------------+------------------+-----------------------+
在结果表上，仅应显示符合审计条件的数字.这是出问题的另一个原因.
On result table, only audit eligible numbers should show up. This is another reason where am going wrong.
谢谢.
谢谢，
里查
推荐答案
您可以使用多个公用表表达式，分别计算每个表并将它们联接在一起-只是为了您了解正在发生的事情.
You may use multiple common table expressions, computing each separately and joining together - just for you to understand what's going on.
查询:
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
结果 :
Results:
| 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) |
这篇关于Oracle SQL-查询以从多个表中计算值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
