长SQL字符串分解为多个变量的优化方案

a = “SELECT (SELECT COUNT(*) FROM (“

b = ” SELECT 成本中心2 AS 部门, 收款方 AS 申请人, 单据公司, SUM(核定金额) AS 金额, 本位币”

c = ” FROM [费用明细报表$]”

d = ” GROUP BY 成本中心2, 收款方, 单据公司, 本位币”

e = “) AS T2 WHERE (T2.部门 < T1.成本中心2)”

f = ” OR (T2.部门 = T1.成本中心2 AND T2.申请人 <= T1.申请人)”

g = “) AS 序号, T1.成本中心2 AS 部门, T1.申请人, T1.金额,”

h = “T1.本位币 AS 币种, T1.单据公司 FROM (“

i = “SELECT 成本中心2, 收款方 AS 申请人, SUM(核定金额) AS 金额, 本位币,”

j = “单据公司, IIF(成本中心2 LIKE '%销售部', 0, 1) AS SortGroup, 0 AS SortOrder”

k = “FROM [费用明细报表$] GROUP BY 成本中心2, 收款方, 单据公司, 本位币”

l = “UNION ALL SELECT '' AS 成本中心2, '合计' AS 申请人,”

m = “SUM(核定金额) AS 金额, 'CNY' AS 本位币, '' AS 单据公司,”

n = “1 AS SortGroup, 1 AS SortOrder FROM [费用明细报表$]”

o = “) AS T1 ORDER BY T1.SortOrder ASC, T1.SortGroup ASC,”

p = “T1.成本中心2 ASC, T1.申请人 ASC”

' 最终拼接

sqlQuery = a & b & c & d & e & f & g & h & i & j & k & l & m & n & o & p

“`

优化说明：

1. 分块原则：

– 按SQL逻辑结构拆分（SELECT/WHERE/UNION/ORDER BY）

– 每段包含完整子句（如`GROUP BY`单独分段）

– 字段列表按语义分组（如聚合字段与排序字段分离）

2. 长度控制：

– 最长段`b`为99字符（含空格）

– 最短段`c`为38字符

– 平均段长72字符

3. 关键处理：

– 子查询别名`T2`/`T1`保留在关联段

– 排序条件`ORDER BY`分两段确保可读性

– 联合查询`UNION ALL`单独分段明确结构

4. 维护优势：

“`vba

' 示例：修改排序逻辑（仅调整o/p段）

o = “) AS T1 ORDER BY T1.SortGroup DESC,” ' 修改此处

p = “T1.SortOrder ASC, T1.成本中心2 ASC” ' 调整顺序

“`

> 提示：实际使用时提议添加缩进符`vbCrLf`和空格提升可读性：

> “`vba

> sqlQuery = a & vbCrLf & b & vbCrLf & c & vbCrLf & d & _

> vbCrLf & e & vbCrLf & f & vbCrLf & g & “…”

> “` “`