mysql - SQL - 使用非聚合表连接聚合表

Question

我编写了以下代码来选择每笔贷款的剩余利息总额:

SELECT
a.PARENTREF,
a.INTEREST - Isnull(b.INTEREST,0) AS INTEREST

FROM
(SELECT 
PARENTREF,
SUM(TOTAL) AS INTEREST
   FROM LG_011_BNCREPAYTR 
   WHERE TRANSTYPE = 0 
   GROUP BY PARENTREF) a LEFT OUTER JOIN
(SELECT
PARENTREF,
SUM(TOTAL) AS INTEREST
   FROM LG_011_BNCREPAYTR 
   WHERE TRANSTYPE = 1 
   GROUP BY PARENTREF) b
ON a.PARENTREF = b.PARENTREF

它带来了如下表格:

PARENTREF       INTEREST
        1         500.93
        2         510.00
        3        1200.90
        4         500.93

现在，数据库中还有另一个表，其中包含有关贷款的其他信息，例如贷款描述。该表的 LOGICALREF 是该表中的 PARENTREF。该表如下:

 LOGICALREF         DESCRIPTION        CURRENCYREF           DUEDATE
          1            CAR LOAN                  0        2015-10-01
          2       CONSUMER LOAN                  2        2015-10-01
          3            CAR LOAN                  4        2015-10-01
          4            CAR LOAN                  3        2015-10-01

我正在尝试将我的表与我提到的最后一个表合并，但由于某种原因，我得到了 DESCRIPTION、CURRENCYREF 和 DUEDATE 为 NULL。我认为由于我使用的 GROUP BY 出现了问题，因为如果我不聚合，这些函数就会起作用。我想要的表格如下:

PARENTREF       INTEREST          DESCRIPTION     CURRENCYREF       DUEDATE 
        1         500.93             CAR LOAN               0    2015-10-01
        2         510.00        CONSUMER LOAN               2    2015-10-01
        3        1200.90             CAR LOAN               4    2015-10-01
        4         500.93             CAR LOAN               3    2015-10-01

非常感谢。

Answer 1

试试这个:

SELECT a.PARENTREF, a.INTEREST - ISNULL(b.INTEREST, 0) AS INTEREST, 
       T.DESCRIPTION, T.CURRENCYREF, T.DUEDATE 
FROM YourTable T 
INNER JOIN (SELECT PARENTREF, SUM(TOTAL) AS INTEREST
            FROM LG_011_BNCREPAYTR 
            WHERE TRANSTYPE = 0 
            GROUP BY PARENTREF) A ON T.LOGICALREF = A.PARENTREF
LEFT OUTER JOIN (SELECT PARENTREF, SUM(TOTAL) AS INTEREST
                 FROM LG_011_BNCREPAYTR 
                 WHERE TRANSTYPE = 1 
                 GROUP BY PARENTREF
                ) b ON a.PARENTREF = b.PARENTREF;

或

您还可以使用以下查询找到解决方案

SELECT A.PARENTREF, 
      (SUM(CASE WHEN A.TRANSTYPE = 0 THEN A.TOTAL ELSE 0 END) - SUM(CASE WHEN A.TRANSTYPE = 1 THEN A.TOTAL ELSE 0 END)) AS INTEREST, 
       T.DESCRIPTION, T.CURRENCYREF, T.DUEDATE 
FROM YourTable T 
INNER JOIN LG_011_BNCREPAYTR A ON T.LOGICALREF = A.PARENTREF 
GROUP BY A.PARENTREF;