在MySQL中如何用WITH语句（CTE）替换变量以创建视图

错误原因说明 #

你之前的写法报错是因为MySQL的WITH子句仅允许出现在查询块的最开头位置，不能嵌套在SELECT的列表达式、WHERE条件等位置，这是语法规则限制。

变量替换通用方法 #

针对不同的变量使用场景，对应两种通用替换方案：

• 场景1：变量为逐行关联计算的标量值（如你当前场景，每一行sa_statement_balances记录对应独立的@sss/@ddd值）
  优先使用预聚合CTE+关联查询的方式：先将所有需要的关联值用CTE批量聚合计算完成，再和主表做JOIN，避免每行重复执行子查询，性能更优，也方便后续复用计算结果。
• 场景2：变量为全局单值常量（如全表统计的总金额、最大日期等）
  直接写返回单值的CTE，后续查询逻辑中通过CROSS JOIN该CTE即可拿到全局值复用，等价于原来的全局变量。

当前场景修复后代码 #

我们用预聚合CTE先把每个sb.id对应的Src和Dst计算完成，再和主表关联，最终代码可以直接封装为视图：

CREATE VIEW v_statement_balance_check AS
WITH journal_agg AS (
  SELECT
    COALESCE(jg.Statement_s, jg.Statement_d) AS statement_id,
    CASE 
      WHEN jg.Source = COALESCE(jg.Statement_s, jg.Statement_d) THEN jg.Source
      WHEN jg.Destination = COALESCE(jg.Statement_s, jg.Statement_d) THEN jg.Destination
    END AS account,
    SUM(CASE WHEN jg.Source = account THEN jg.Amount ELSE 0 END) AS Src,
    SUM(CASE WHEN jg.Destination = account THEN jg.Amount ELSE 0 END) AS Dst
  FROM sa_general_journal jg
  GROUP BY statement_id, account
)
SELECT
  sb.id,
  sb.Account,
  acct_name(sb.Account) AS `Name`,
  sb.`Begin`,
  sb.Begin_balance,
  sb.`End`,
  sb.End_balance,
  sb.amount `Change`,
  COALESCE(ja.Src, 0) AS `Src`,
  COALESCE(ja.Dst, 0) AS `Dst`,
  CONVERT(COALESCE(ja.Dst, 0) - COALESCE(ja.Src, 0), DECIMAL (8,2)) AS `Dest-Src`,
  IF(
    CONVERT(COALESCE(ja.Dst, 0) - COALESCE(ja.Src, 0) - sb.amount, DECIMAL (8,2)) = 0,
    NULL,
    CONVERT(COALESCE(ja.Dst, 0) - COALESCE(ja.Src, 0) - sb.amount, DECIMAL (8,2))
  ) AS `Error`
FROM sa_statement_balances sb
LEFT JOIN sa_accounts a ON sb.Account = a.ID
LEFT JOIN journal_agg ja ON sb.id = ja.statement_id AND sb.account = ja.account
# WHERE `Begin` >= '2020-07-01'
GROUP BY sb.id DESC
ORDER BY sb.`Begin`;

如果想保留原关联子查询的写法也可以，直接去掉变量，把子查询逻辑重复写到用到@sss和@ddd的位置即可，只是性能低于预聚合CTE方案。

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