我有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 JOINs,因此也会这样做。

关于sql - 如何在firebird的两个不同表中获得两个不同的总数? (使用左外部联接联接3个表),我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44519348/

10-12 12:43
查看更多