我目前撰寫了一個查詢，該查詢使用 UNION ALL 添加多個選擇陳述句并回傳每個選擇陳述句中的金額總和。我的問題：有什么方法或方法可以使這個查詢更小（不是那么長）？
每個子查詢中唯一改變的是用于從 Accnt 表中獲取 ID1 的 ID#
SELECT SUM(TOTAL) TOTAL
FROM
(
SELECT ISNULL (round(sum(signeddata),2),0)
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
SELECT ID1 FROM DataSrc
WHERE ID7 = 'Logic')
AND ACCOUNT IN (
select ID1 from Accnt
where ID7 = 'EXPENSES')
AND ENTITY IN (
SELECT ID1 FROM Entity
where ID9 = 'H2')
UNION ALL
SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
SELECT ID1 FROM DataSrc
WHERE ID7 = 'Logic')
AND ACCOUNT IN (
select ID1 from Accnt
where ID7 = 'MERC')
AND ENTITY IN (
SELECT ID1 FROM Entity
where ID9 = 'H2')
UNION ALL
SELECT ISNULL (round(sum(signeddata),2),0)*-1
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
SELECT ID1 FROM DataSrc
WHERE ID7 = 'Logic')
AND ACCOUNT IN (
select ID1 from Accnt
where ID11 = 'SYNP')
AND ENTITY IN (
SELECT ID1 FROM Entity
where ID9 = 'H2')
) S
uj5u.com熱心網友回復：
一種想法是對 case-semijoins 求和而不是將它們放在FROM子句中：
SELECT
ISNULL(round(sum(signeddata),2),0)
*
(
case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'EXPENSES')
then 1 else 0 end * 1
case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'MERC')
then 1 else 0 end * (-1)
case when ACCOUNT IN (select ID1 from Accnt where ID7 = 'SYNP')
then 1 else 0 end * (-1)
)
FROM BUDGET
WHERE CATEGORY = 'ACTL'
AND TIME BETWEEN '20210100' AND '20210800'
AND CURRENCY = 'DOL'
AND DATASRC IN (
SELECT ID1 FROM DataSrc
WHERE ID7 = 'Logic')
AND ENTITY IN (
SELECT ID1 FROM Entity
where ID9 = 'H2')
uj5u.com熱心網友回復：
您可以只IN用于ID7值
始終為列指定表參考，尤其是在相關子查詢上
SELECT
ISNULL(round(sum(b.signeddata), 2), 0) TOTAL
FROM BUDGET b
WHERE b.CATEGORY = 'ACTL'
AND b.TIME BETWEEN '20210100' AND '20210800'
AND b.CURRENCY = 'DOL'
AND b.DATASRC IN (
SELECT ds.ID1 FROM DataSrc ds
WHERE ds.ID7 = 'Logic')
AND b.ACCOUNT IN (
select a.ID1 from Accnt a
where a.ID7 IN ('EXPENSES', 'MERC', 'SYNP'))
AND b.ENTITY IN (
SELECT e.ID1 FROM Entity e
where e.ID9 = 'H2');
請注意，如果您這樣做，四舍五入會略有不同
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/365112.html
上一篇：如何向后計算sql中的運行總數