SQL:联接表后SUM()函数返回错误的值 [英] SQL: After joining tables SUM() function returns wrong value

问题描述

我目前正在从事数据库项目，并且在连接表方面遇到一些问题.最初的情况是:

I am currently working on a database project and have some issues with joining tables. The initial situation is:

四个表:

• 任务t〜50000条记录
• 计划p〜1000条记录
• workson w〜30000条记录
• 员工e〜10000条记录

表w具有一个称为"WORKLOAD"的属性，不幸的是SUM(w.WORKLOAD)的结果不是预期的:

Table w has an attribute called something like "WORKLOAD", unfortunately the result of SUM(w.WORKLOAD) is not the one expected:

SELECT
p.NAME,
SUM(w.WORKLOAD) AS "Total Workload",
COUNT(DISTINCT w.ESSN) AS "Total Employees",
COUNT(DISTINCT t.NAME) AS "Finished Tasks" --t.NAME is unique
from p 
JOIN w ON(p.PNUMBER = w.PNO)
JOIN t ON(p.PNUMBER = t.PNO)
WHERE t.END_DATE is NOT NULL
GROUP BY p.PNUMBER, p.NAME

连接这些表后，SUM()函数返回的值太大.我猜这是因为SUM()函数多次计数每个w.WORKLOAD值.

After joining these tables, the SUM() function returns a far too big value. I guess that's because the SUM() function counts each w.WORKLOAD value multiple times.

那么是否有诸如内部联接之类的联接操作可以在不使用子查询的情况下解决此问题?

在此先感谢:-)

So is there any join operation like inner join that can fix the issue without using subqueries?

Thanks in Advance :-)

推荐答案

问题在于笛卡尔乘积(其中一个表中的行与另一个表中的行相乘).采用以下方法的假设是，每个项目都有一个分配了员工的工作负载(由于查询未显示到employee表的联接，所有工作都分配给了所有员工).如果不是这种情况，请考虑进行外部联接还是内部联接.

At issue is a Cartesian product (where rows in one table are being multiplied by the rows in the other tables). The assumption the following approach is making is that every project has a workload with employees assigned (all of which account for all employees since your query doesn't show the join to the employee table) and tasks. If this isn't the case, then consider doing outer joins versus the inner join.

该想法是根据项目编号在其自己的派生表中执行每个聚合.然后，我们可以按项目编号连接每个派生表，以获得有意义的结果.

The idea is to perform each aggregation in its own derived table based on project number. We can then join each derived table by project number to obtain meaningful results.

SELECT
p.NAME,
w.workload_sum AS "Total Workload",
e.employee_count AS "Total Employees",
t.task_count AS "Finished Tasks"
from p 
JOIN (select pno, sum(workload) as workload_sum
        from w
       group by pno) w ON (w.pno=p.pnumber)
JOIN (select pno, count(distinct w.essn) as employee_count
        from w
       group by pno) e ON (e.pno=p.pnumber)
JOIN (select pno, count(distinct t.name) as task_count
        from t
       group by pno) t ON (t.pno=p.pnumber)
WHERE t.END_DATE is NOT NULL;

这篇关于SQL:联接表后SUM()函数返回错误的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！