我有3张表,样本数据如下
Table #1 EMPLOYEE
EMPLOYEE ID EMPLOYEE_NAME
1 Juan Dela Cruz
2 Jobert Saver
Table #2 ADD_TABLE
ADD_TABLE_PK EMPLOYEE_ID ADD_AMOUNT
1 1 10.00
2 1 13.00
Table #3 SUBTRACT_TABLE
SUBTRACT_PK EMPLOYEE_ID SUBTRACT_AMOUNT
1 1 2.00
2 1 3.00
3 1 4.00
我想要的输出是这样的:
SELECT PROCEDURE
EMPLOYEE_ID EMPLOYEE_NAME TOTAL(Total = sum of ADD_TABLE.ADD_AMOUNT - sum of SUBTRACT_TABLE.SUBTACT_AMOUNT)
1 Juan Dela Cruz 14.00 ( = 23.00 - 9.00)
但我的选择程序的结果是:
EMPLOYEE_ID EMPLOYEE_NAME TOTAL
1 Juan Dela Cruz 51.00
这是我使用左外连接的select procuder
CREATE PROCEDURE SAMPLE_SELECT
RETURNS(
EMPLOYEE_ID INTEGER,
EMPLOYEE_NAME VARCHAR(50) CHARACTER SET ISO8859_1 COLLATE ISO8859_1,
TOTAL DECIMAL(12, 2))
AS
BEGIN
FOR
SELECT
A.EMPLOYEE_ID,
A.EMPLOYEE_NAME,
SUM(B.ADD_AMOUNT) - SUM(C.SUBTRACT_AMOUNT)
FROM EMPLOYEE A
LEFT OUTER JOIN ADD_TABLE B ON A.EMPLOYEE_ID = B.EMPLOYEE_ID
LEFT OUTER JOIN SUBTRACT_TABLE C ON A.EMPLOYEE_ID = C.EMPLOYEE_ID
GROUP BY
A.EMPLOYEE_ID,
A.EMPLOYEE_NAME
INTO
:EMPLOYEE_ID,
:EMPLOYEE_NAME,
:TOTAL
DO
BEGIN
SUSPEND;
END
END;
最佳答案
在加入表之前,需要进行聚合:
select e.*, coalesce(add_amount, 0) - coalesce(subtract_amount, 0)
from employee e left join
(select employee_id, sum(add_amount) as add_amount
from add_table a
group by employee_id
) a
on a.employee_id = e.employee_id left join
(select employee_id, sum(subtract_amount) as subtract_amount
from subtract_table s
group by employee_id
) s
on s.employee_id = e.employee_id;
注:这将保留所有员工。您的查询尝试使用
LEFT JOIN
s,因此也会这样做。关于sql - 如何在firebird的两个不同表中获得两个不同的总数? (使用左外部联接联接3个表),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44519348/