如何从含子查询的SQL统计语句中获取given、used两列的合计总值

调整后的SQL查询（直接返回两列总计值） #

SELECT
  SUM(b.aGiven) AS total_given,
  SUM(b.aUsed) AS total_used
FROM (
    SELECT
      SUM(ua.UserAccountUsedAmount) AS aUsed,
      0 AS aGiven
    FROM UserAccounts AS ua
    LEFT OUTER JOIN UserAccountCodes AS uac1 ON ua.UserAccountCode = uac1.UserAccountCode
    WHERE uac1.UserAccountCodeCreatedOn between '07-11-2020' and '07-11-2021'

    UNION ALL -- 替换原UNION，省去不必要的去重计算，性能更优

    SELECT
      0 AS aUsed,
      SUM(uac.UserAccountCodeAmount) AS aGiven
    FROM UserAccountCodes AS uac
    LEFT OUTER JOIN UserAccounts AS ua1 ON uac.UserAccountCode = ua1.UserAccountCode
    WHERE uac.UserAccountCodeCreatedOn between '07-11-2010' and '07-11-2021'
) AS b

核心调整说明 #

• 去掉了外层与UserAccountGroups表的关联逻辑，同时删除了GROUP BY uag.ReportGroup分组语句，适配你不需要分组统计的需求
• 去掉了子查询中对UserAccountGroupID的分组和查询逻辑，总合计不需要按分组维度拆分，直接对两个子查询的聚合结果做全局求和即可
• 建议将原UNION修改为UNION ALL：两个子查询返回的行不存在重复（一行仅aUsed有值、aGiven为0，另一行仅aGiven有值、aUsed为0），UNION ALL无需执行去重逻辑，查询效率更高
• 后续需要传入自定义日期范围时，直接替换两个WHERE子句中的日期常量为codebehind的传入参数即可，整体查询结构无需调整

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