多库UNION查询重复参数优化：如何全局设置统一AcctCode筛选条件

当然可以，你可以通过以下方案实现AcctCode列表的全局复用，彻底消除重复代码：

方案1：使用CTE（公用表表达式）定义全局账号列表 #

绝大多数主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle等）都支持CTE语法，你可以在查询最开头一次性定义所有要筛选的AcctCode，后续所有子查询直接复用即可，同时还可以顺带把重复的日期条件也统一处理：

WITH target_accts AS (
    SELECT '105004001' AS AcctCode UNION ALL
    SELECT '105005001' UNION ALL
    SELECT '105006001' UNION ALL
    SELECT '105007001' UNION ALL
    SELECT '105008001' UNION ALL
    -- 剩余需要的账号都统一加在这里即可，后续不用重复写
    SELECT '105009001' UNION ALL
    SELECT '105104001' UNION ALL
    SELECT '105105001' UNION ALL
    SELECT '105106001' UNION ALL
    SELECT '105107001' UNION ALL
    SELECT '105108001' UNION ALL
    SELECT '105109001' UNION ALL
    SELECT '106001001' UNION ALL
    SELECT '107009001' UNION ALL
    SELECT '109018001' UNION ALL
    SELECT '109022001' UNION ALL
    SELECT '201001001' UNION ALL
    SELECT '201002001'
)
SELECT 
    'Database1' as DataSource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
FROM Db1.Table1 T0  
INNER JOIN Db1.Table2 T1 ON T0."Account" = T1."AcctCode" 
INNER JOIN Db1.Table3 T2 ON T0."TransId" = T2."TransId"
INNER JOIN target_accts ta ON T1."AcctCode" = ta.AcctCode
WHERE 
    '01.01.0001' <= '01.07.2020'  
    AND '31.03.2021' >= T0."RefDate"  
GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

SELECT 
    'Database2' As DataSource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
FROM Db2.Table1 T0  
INNER JOIN Db2.Table2 T1 ON T0."Account" = T1."AcctCode" 
INNER JOIN Db2.Table3 T2 ON T0."TransId" = T2."TransId"
INNER JOIN target_accts ta ON T1."AcctCode" = ta.AcctCode
WHERE 
    '01.01.0001' <= '01.07.2020'  
    AND '31.03.2021' >= T0."RefDate"  
GROUP BY T1."AcctCode",T1."AcctName"

UNION ALL

SELECT 
    'Database3' As DataSource,T1."AcctCode",T1."AcctName", SUM(T0."Debit") - SUM(T0."Credit") as TotalBal
FROM Db3.Table1 T0  
INNER JOIN Db3.Table2 T1 ON T0."Account" = T1."AcctCode" 
INNER JOIN Db3.Table3 T2 ON T0."TransId" = T2."TransId"
INNER JOIN target_accts ta ON T1."AcctCode" = ta.AcctCode
WHERE 
    '01.01.0001' <= '01.07.2020'  
    AND '31.03.2021' >= T0."RefDate"  
GROUP BY T1."AcctCode",T1."AcctName"

如果你使用的数据库支持自定义变量，还可以把重复的日期常量也定义成变量，后续修改只需要改一处即可。
如果不支持CTE，也可以用临时表存储目标账号列表，效果和CTE一致，适合账号数量较多的场景。如果这组账号是业务常用的固定编码，还可以专门建一个配置表存储，后续调整账号筛选范围只需要修改配置表数据，不需要改动SQL代码。

注：你之前重构后的代码里第三个查询的DataSource字段拼写错误为DataDource，优化时可以同步修正。

内容的提问来源于stack exchange，提问作者Honey Singh