SQL 将两个 SUM 查询的结果用于一个查询

Question

也许这是一个简单的问题，但我不知道该怎么做。 我有 2 个 SQL 查询，两者都几乎相同，它们仅在 WHERE 子句中不同。 每个查询返回 1 个 SUM 编号。 我需要从两个查询中获取结果，并使用它来制作 1 个名为"RESULT"的列，其中将计算查询的结果。

1 个查询：

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p
ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c
ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co
on c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por
ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE co.COLTPTF_KEY = '261' AND 
co.COLT_DELETED_FLAG = 'N' AND 
a.ACC_CLOSE_FLAG = 'N' 
AND  co.COLT_SHORTENING_COEFFICIENT = 82

2 查询：

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p
ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c
ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co
on c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por
ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE co.COLTPTF_KEY = '261' AND 
co.COLT_DELETED_FLAG = 'Y' AND 
a.ACC_CLOSE_FLAG = 'Y' 
AND  co.COLT_SHORTENING_COEFFICIENT = 82

假设第一个查询返回 SUM 值 10，第二个查询返回 5。 所以我需要查询，它将返回 15 的列 RESULT。

也许有一些更有效的方法来编写此查询，因为它仅在两个参数的 WHERE 子句中不同。

感谢您的帮助

Answer 1

这应该可以完成工作：

SELECT SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co ON c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE 
co.COLTPTF_KEY = '261' 
AND (a.ACC_CLOSE_FLAG, co.COLT_DELETED_FLAG) IN ( ('Y', 'Y'), ('N', 'N') )
AND  co.COLT_SHORTENING_COEFFICIENT = 82

您可以使用条件聚合一次获得 3 个结果：

SELECT 
SUM(CASE WHEN a.ACC_CLOSE_FLAG = 'N' AND co.COLT_DELETED_FLAG = 'N' 
THEN a.ACC_NOMINAL_AMOUNT END) AS RESULT_N,
SUM(CASE WHEN a.ACC_CLOSE_FLAG = 'Y' AND co.COLT_DELETED_FLAG = 'Y' 
THEN a.ACC_NOMINAL_AMOUNT END) AS RESULT_Y,
SUM(a.ACC_NOMINAL_AMOUNT) AS RESULT
FROM ACCOUNTS a 
INNER JOIN PARTIES p ON a.PT_KEY = p.PT_KEY
INNER JOIN ACCOUNT_COLLATERALS c ON c.ACC_KEY = a.ACC_KEY
INNER JOIN COLLATERALS co ON c.COLT_KEY = co.COLT_KEY
INNER JOIN COLLATERAL_PORTFOLIOS por ON co.COLTPTF_KEY = por.COLTPTF_KEY
WHERE 
co.COLTPTF_KEY = '261' 
AND (a.ACC_CLOSE_FLAG, co.COLT_DELETED_FLAG) IN ( ('Y', 'Y'), ('N', 'N') )
AND  co.COLT_SHORTENING_COEFFICIENT = 82

Answer 2

您可以轻松地组合条件：

SELECT SUM(a.ACC_NOMINAL_AMOUNT)
FROM . . .
WHERE co.COLTPTF_KEY = '261' AND 
co.COLT_SHORTENING_COEFFICIENT = 82 AND
(co.COLT_DELETED_FLAG, a.ACC_CLOSE_FLAG) IN ( ('N', 'N'), ('Y', 'Y') )