谁能告诉我为什么它显示错误的金额? [英] can any one tell me why its show wrong sum?

问题描述

SQL> select * from employee;

NAME       EADD              EID     SALARY
---------- ---------- ---------- ----------
himanshu   sre                 1       4000
archana    jammu               2       3000
sanjana    delhi               3       2500
pooja      gng                 4       2600
bhavdeep   ynr                 5       1600

SQL> select * from project;

PNAME      DESCRIPTIO        PID       COST
---------- ---------- ---------- ----------
management manageall           1       1000
sap        spadmsn            30        300
javaadsn   adsn               40        400
.net       adsnnet            40        400


SQL> select sum(e.salary) , sum(p.cost) from employee e , project p;

SUM(E.SALARY) SUM(P.COST)
------------- -----------
        54800       10500

推荐答案

原因是该语句

The reason is that the statement

select sum(e.salary) , sum(p.cost) from employee e , project p;

产生Full Outer Join，由于表employee中的行各为repeated 4 times，因此Sum(e.salary)= 4 * 13700 = 54800，表项目中的行各为repeated 5 times，因此Sum( p.cost)= 5 * 2100 = 10500.

makes a Full Outer Join, due to which the rows in table employee are repeated 4 times each, hence the Sum(e.salary) = 4 * 13700 = 54800 and the rows in table project are repeated 5 times each so that the Sum(p.cost) = 5 * 2100 = 10500.

是.

10500 / 5 = 2300
1000 + 300 + 400 + 400 = 2300.
There are five records in the first table.

54800 / 4 = 13700
4000 + 3000 + 2500 + 2600 + 1600 = 13700
There are four records in the second table.

我不确定查询应该是什么，但这就是为什么要得到结果的原因.
试试这个:

I''m not sure what the query should be, but that is why you are getting the result you are.

Try this:

select sum(salary) , (SELECT sum(cost) from  project) FROM employee

您可以做到也可以通过不同的方式来定义列标题和行标题，并使用 UNION [ ^ ]命令:

You can do it in different way too, defining column-headers and row-headres and using UNION[^] command:

SELECT 'Salary' AS [Item], SUM([Salary]) AS [Sum]
FROM employee
UNION ALL
SELECT 'Cost' AS [Item], SUM([Cost]) AS [Sum]
FROM project

结果:

Item	Sum
Salary	13700
成本	2300

Results:

Item	Sum
Salary	13700
Cost	2300

这篇关于谁能告诉我为什么它显示错误的金额?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！