添加多个select语句而不使用Union ALL

Question

我目前编写了一个查询，该查询使用UNION ALL添加多个select语句，并返回每个select语句中的金额总和。我的问题是：有没有什么方法可以缩小这个查询(不要那么长(？

每个子查询中唯一更改的是用于从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

Answer 1

一个想法是对大小写半联接求和，而不是将它们放在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')

Answer 2

您可以仅将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');

注意，如果你像这个那样做，舍入会略有不同