如何在SELECT语句中模块化子查询定义以复用计算逻辑？

这确实是维护复杂SQL时的典型痛点——重复粘贴大段子查询不仅容易写错，后续要修改逻辑时还得找遍所有重复的地方，简直太折腾人了！下面给你几个能高效复用calc1、calc2、calc3逻辑的方案：

1. 优先用CTE（公共表表达式）——可读性拉满 #

CTE是最推荐的方式，它能把重复的计算逻辑提前封装成一个临时结果集，主查询直接引用就行，逻辑清晰到谁看都懂：

WITH billing_calculations AS (
    SELECT 
        billings.id,
        (very complex calc1) AS amount,
        (very complex calc2) AS paid_online,
        (very complex calc3) AS paid_by_check
    FROM billings
    GROUP BY billings.id
)
SELECT 
    amount,
    paid_online,
    paid_by_check,
    amount - paid_online - paid_by_check AS amount_due
FROM billing_calculations;

之后要修改calc1的计算逻辑？只需要在CTE里改一次就行，完全不用管主查询，维护起来超级省心。

2. 子查询——兼容旧版本数据库 #

如果你的数据库不支持CTE（比如某些老版本的MySQL），用子查询也能达到同样的效果，本质和CTE是一样的，只是写法不同：

SELECT 
    bc.amount,
    bc.paid_online,
    bc.paid_by_check,
    bc.amount - bc.paid_online - bc.paid_by_check AS amount_due
FROM (
    SELECT 
        billings.id,
        (very complex calc1) AS amount,
        (very complex calc2) AS paid_online,
        (very complex calc3) AS paid_by_check
    FROM billings
    GROUP BY billings.id
) AS bc;

把计算逻辑塞进子查询，给子查询起个别名bc，主查询直接用别名引用计算结果就行。

3. 用户定义函数（UDF）——跨查询复用 #

如果这些计算逻辑不止在这一个查询里用到，而是多个地方都需要，那写个UDF就更合适了，相当于把计算逻辑封装成一个可调用的“工具”：

先创建三个函数（以MySQL为例，不同数据库语法略有差异）：

CREATE FUNCTION calculate_amount(billing_id INT) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    -- 这里替换成你的very complex calc1逻辑，用billing_id关联子查询
    RETURN (SELECT SUM(...) FROM payment_details WHERE billing_id = billings.id);
END;

CREATE FUNCTION calculate_paid_online(billing_id INT) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    -- 替换成very complex calc2逻辑
    RETURN (SELECT SUM(...) FROM online_payments WHERE billing_id = billings.id);
END;

CREATE FUNCTION calculate_paid_by_check(billing_id INT) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    -- 替换成very complex calc3逻辑
    RETURN (SELECT SUM(...) FROM check_payments WHERE billing_id = billings.id);
END;

然后查询的时候直接调用函数：

SELECT 
    calculate_amount(billings.id) AS amount,
    calculate_paid_online(billings.id) AS paid_online,
    calculate_paid_by_check(billings.id) AS paid_by_check,
    calculate_amount(billings.id) - calculate_paid_online(billings.id) - calculate_paid_by_check(billings.id) AS amount_due
FROM billings
GROUP BY billings.id;

这样不管哪个查询需要这些计算，直接调用函数就行，逻辑改一次全生效。不过要注意：有些数据库里UDF的性能可能不如CTE/子查询，需要根据实际情况测试。

内容的提问来源于stack exchange，提问作者Oliver G. Reid