我希望能够列出st.balancechange的总和，但针对不同的s.type。运行查询大约需要7分钟。我肯定有更好的方法来优化它，但我不知道如何优化。我不能将其用作子查询，因为我需要返回多个结果。
SELECT DISTINCT a.ACCOUNTNUMBER AS [Account Number]
, s1.Deposit AS [Aggregate Share Balance]
, c.Deposit AS [Aggregate Checking Balance]
FROM dbo.ACCOUNT a
JOIN dbo.SAVINGS s
ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
AND a.ProcessDate = s.ProcessDate
JOIN (SELECT a.ACCOUNTNUMBER
, SUM(st.BALANCECHANGE) AS [Deposit]
FROM dbo.ACCOUNT a
JOIN dbo.SAVINGS s
ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
AND a.ProcessDate = s.ProcessDate
JOIN dbo.SAVINGSTRANSACTION st
ON st.PARENTACCOUNT = s.PARENTACCOUNT
WHERE a.ProcessDate = CONVERT(VARCHAR(8), DATEADD(DAY,-1, GETDATE()), 112)
AND a.CLOSEDATE IS NULL
AND s.CLOSEDATE IS NULL
AND st.ACTIONCODE = 'D'
AND s.TYPE IN (0, 1, 2, 3, 4, 6, 100)
GROUP BY a.ACCOUNTNUMBER) s1 ON s1.ACCOUNTNUMBER = a.ACCOUNTNUMBER
LEFT JOIN (SELECT a.ACCOUNTNUMBER
, SUM(st.BALANCECHANGE) AS [Deposit]
FROM dbo.ACCOUNT a
JOIN dbo.SAVINGS s
ON a.ACCOUNTNUMBER = s.PARENTACCOUNT
AND a.ProcessDate = s.ProcessDate
JOIN dbo.SAVINGSTRANSACTION st
ON st.PARENTACCOUNT = s.PARENTACCOUNT
WHERE a.ProcessDate = CONVERT(VARCHAR(8), DATEADD(DAY,-1, GETDATE()), 112)
AND a.CLOSEDATE IS NULL
AND s.CLOSEDATE IS NULL
AND st.ACTIONCODE = 'D'
AND s.TYPE IN (2, 100, 101, 102, 103, 104, 105, 106, 107, 108, 113, 150, 201)
GROUP BY a.ACCOUNTNUMBER) c ON c.ACCOUNTNUMBER = a.ACCOUNTNUMBER
WHERE a.ProcessDate = CONVERT(VARCHAR(8), dateadd(day,-1, getdate()), 112)
AND a.CLOSEDATE IS NULL
AND s.CLOSEDATE IS NULL
ORDER BY a.ACCOUNTNUMBER当前结果：
Account Number Aggregate Share Balance Aggregate Checking Balance
0000001234 7383.58 NULL
0000001235 95856.83 95856.83
0000001236 123.27 123.27
0000001237 1.88 NULL
0000001238 14812.26 NULL
0000001239 15.00 NULL
0000001240 4.06 NULL
0000001241 6.42 3.21
0000001242 3705.03 3705.03
0000001243 976841.06 1465261.59
1条答案
按热度按时间x6yk4ghg1#
我认为你不需要两次查询余额。两个子查询之间的唯一区别是类型。您可以尝试类似于下面的操作，并且只能查询一次。我还认为distinct是没有必要的，因为如果删除子查询，您可以按帐号对主查询进行分组。