SQL多个表变量组合与循环统计逻辑的简化优化方案咨询

优化后等价代码 #

WITH 
-- 统计每个小队的场景量
SceneStats AS (
    SELECT SceneSquad AS Squad, COUNT(*) AS SceneCount
    FROM (
        SELECT A.IncNo, SceneSquad, ScDate 
        FROM DATABASE as A
        LEFT JOIN DATABASE as B On A.IncNo = B.IncNo
        WHERE ScDate BETWEEN '2021-01-01' AND '2021-10-31' AND A.DivID = ''
    ) t
    GROUP BY SceneSquad
),
-- 统计每个小队的指派任务量
AssignStats AS (
    SELECT Followupsquad AS Squad, COUNT(*) AS AssignCount
    FROM (
        SELECT IncNo, Followupsquad, ASSIGNMENTDATE
        FROM DATABASE AS C
        LEFT JOIN DATABASE AS D ON D.IncNo = C.CIncNo
        WHERE SOMETHING = 'Y' AND DateRuled BETWEEN '2021-01-01' AND '2021-10-31' AND D.AssignD = ''
    ) t
    GROUP BY Followupsquad
),
-- 所有有效小队列表
ValidSquads AS (
    SELECT Squad
    FROM DATABASE
    WHERE UnitType = '' AND DivId = '' AND SquadStatus = 'A'
)
-- 关联得到最终结果
SELECT 
    s.Squad AS SqdNo,
    ISNULL(a.AssignCount, 0) AS SOMETHING,
    ISNULL(sc.SceneCount, 0) AS SOMETHINGELSE
FROM ValidSquads s
LEFT JOIN AssignStats a ON s.Squad = a.Squad
LEFT JOIN SceneStats sc ON s.Squad = sc.Squad
ORDER BY s.Squad

核心优化点 #

• 摒弃原有逐行循环的低效实现，改用SQL原生集合运算，数据量越大效率提升越明显，避免了循环中多次重复查询中间表的额外开销
• 用CTE拆分各模块统计逻辑，代码结构更清晰，可读性更强
• 移除了不必要的临时表变量、循环控制变量，减少了内存占用和冗余计算开销
• 日期格式统一改为标准的YYYY-MM-DD格式，避免不同数据库实例的日期格式兼容问题
• 补充ISNULL逻辑，确保如果某小队没有对应场景/指派任务时，返回0而非NULL，和原有逻辑返回结果完全一致

兼容说明 #

如果使用的数据库版本不支持CTE语法，可将各CTE块改写为临时表，统计逻辑保持不变即可。

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