在编写复杂业务报表或数据同步类的SQL存储过程时，开发人员经常会遇到同一个过滤条件、聚合逻辑或连接结果在多个步骤中反复出现的情况。如果直接把这段逻辑复制粘贴到每一个查询里，不仅让存储过程变得臃肿，还会在后续需求变更时埋下不同步修改的隐患。通过公共CTE（Common Table Expression）把可复用的数据块提前提取出来，是降低逻辑冗余、提升可读性的有效手段。
为什么存储过程容易产生逻辑冗余
存储过程往往要服务多个业务动作，比如先统计门店销售，再算同比环比，最后写入汇总表。这些步骤如果各自写一遍相同的客户筛选规则，代码就会迅速膨胀。当筛选规则依赖十几个字段判断时，复制出来的每一处都可能因为手误产生细微差别。
从数据库执行角度看，重复的子查询不一定被优化器自动合并。尤其在MySQL旧版本或嵌套较深的写法中，相同子查询可能被执行多次，浪费IO与CPU。把公共逻辑固化成CTE，优化器通常只需物化一次，后续引用直接读中间结果。
公共CTE的基本写法
CTE使用WITH关键字定义，在存储过程开头声明后，后续多条SELECT或UPDATE都能引用。下面以PostgreSQL风格演示提取用户活跃度公共块。
WITH active_user AS (
SELECT
user_id,
COUNT(order_id) AS order_cnt,
SUM(amount) AS total_amount
FROM orders
WHERE create_time >= '2023-01-01'
AND status <> 'canceled'
GROUP BY user_id
)
SELECT u.user_id, a.order_cnt
FROM users u
JOIN active_user a ON u.user_id = a.user_id
WHERE a.total_amount > 1000;
-- 另一个步骤复用同一CTE
SELECT region, AVG(order_cnt)
FROM active_user
GROUP BY region;
上述代码中，active_user只定义一次，却在前后的查询中被连接和聚合。相比把子查询写两遍，维护时只需调整CTE内部即可。
需要注意，CTE在标准中默认每次被引用都可能重新执行，但主流数据库如PostgreSQL、SQL Server会做内联或物化优化。如果明确需要强制物化，可写成MATERIALIZED形式（PostgreSQL 12+）以避免重复计算。
改写前后的对比示例
假设原存储过程存在冗余子查询，我们给出MySQL兼容的改写对照。原逻辑在三个地方重复计算有效订单。
-- 冗余写法示例
SELECT * FROM shop_a
WHERE shop_id IN (
SELECT shop_id FROM orders WHERE amount > 100 AND paid = 1
);
SELECT * FROM shop_b
WHERE shop_id IN (
SELECT shop_id FROM orders WHERE amount > 100 AND paid = 1
);
使用公共CTE提取后：
WITH valid_shop AS (
SELECT DISTINCT shop_id
FROM orders
WHERE amount > 100 AND paid = 1
)
SELECT * FROM shop_a WHERE shop_id IN (SELECT shop_id FROM valid_shop);
SELECT * FROM shop_b WHERE shop_id IN (SELECT shop_id FROM valid_shop);
改写后逻辑集中，且数据库可对valid_shop做临时索引或缓存。对于超大数据表，建议在CTE内先过滤分区字段，避免全表扫描被多次触发。
若存储过程包含写操作，也可将CTE结果插入临时表再使用，但在纯读取汇总场景中，CTE比显式CREATE TEMPORARY TABLE更简洁，也不会留下表清理负担。
与临时表、子查询的方案差异
很多团队习惯用临时表消除冗余。临时表确实能强制只算一次，但增加了DDL语句与权限要求，且在高并发下可能产生元数据锁竞争。CTE则属于查询级结构，随语句结束自动失效。
| 方案 | 冗余控制 | 维护成本 | 适用场景 |
|---|---|---|---|
| 重复子查询 | 差 | 高 | 简单脚本 |
| 临时表 | 好 | 中 | 多步骤大批量写 |
| 公共CTE | 好 | 低 | 只读复用逻辑 |
从执行计划稳定性看，CTE让优化器看到统一的逻辑边界，便于生成一致的执行路径。而分散子查询在统计信息变化剧烈时，可能在不同调用点产生不同计划。
实践中的注意事项
第一，CTE不是万能缓存。在MySQL 5.7及之前，派生表合并规则较保守，过深CTE可能导致性能回退，应结合EXPLAIN验证。第二，如果公共块依赖存储过程入参，直接写在CTE中即可，参数化不会影响提取效果。
第三，避免把过多逻辑堆进一个CTE，否则单块过大反而降低可读性。可按业务含义拆成多个命名清晰的CTE，例如valid_order、user_level分开定义，在最终查询中组合。这样既能复用，也方便单元测试每段逻辑。
最后，在团队规范里明确CTE命名前缀与注释要求，能让后来者快速识别哪些块是公共复用部分，进一步降低协作成本。