Join

如何在內部連接中獲得單行而不使總和增加一倍

  • August 20, 2015

我有兩個表(DEP_DEZ 和 CAR_EXP):

ID  FK_ID_CHESTIONAR    TOTAL_E TOTAL_F TOTAL_STOC
261 644                 87      152     48

ID  MASA_TOTALA FK_ID_CHESTIONAR
405 20          644
406 45          644

如果我做一個:

SELECT CHT.ID,
      SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC)
      FROM CHEST cht
           INNER JOIN DEP_DEZ
              ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
  GROUP BY CHT.ID

結果是 644 和 287 (87+152+48)。沒關係,但如果我做:

SELECT CHT.ID,
      SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC),
      SUM(CAR_EXP.MASA_TOTALA),
      FROM CHEST cht
           INNER JOIN DEP_DEZ
              ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
           INNER JOIN CAR_EXP
              ON CAR_EXP.FK_ID_CHESTIONAR = CHT.ID
  GROUP BY CHT.ID

結果是 644 和 574(287*2 - 不正常)和 65(45+20 - 正常)

我怎樣才能使我的“287”總和不加倍?

您可以使用多個 SELECT 語句,如下所示:

SELECT CHT.ID,
  (SELECT SUM(TOTAL_E + TOTAL_F + TOTAL_STOC) 
  FROM DEP_DEZ WHERE FK_ID_CHESTIONAR = cht.ID),
  (SELECT SUM(MASA_TOTALA) FROM CAR_EXP WHERE FK_ID_CHESTIONAR = CHT.ID)
  FROM CHEST cht
GROUP BY CHT.ID

引用自:https://dba.stackexchange.com/questions/111669