同表SELECT查询如何添加多层计算的PRECIO_NETO_EUR字段

最佳实现方案 #

你的判断完全正确：所有计算依赖的字段全部来自源表同一行数据，不需要任何形式的关联操作。你原来写的多层嵌套子查询虽然逻辑通顺，但存在多层重复扫描源表的性能问题，最优方案是单遍扫描源表完成所有计算，避免嵌套带来的多余开销。

可选实现方案（按推荐优先级排序） #

1. 最高可读性方案（SQL Server环境适用）

用CROSS APPLY逐层拆分计算逻辑，不需要重复书写相同的计算表达式，每一层的计算只依赖上一步的结果，同时仅对TABLE做1次扫描：

SELECT 
  t.*,
  (t.ACUCPRECIOBRUTOMON * t.EUR_RATE) - calcX.X - calcY.Y - calcZ.Z - calcK.K AS PRECIO_NETO_EUR
FROM TABLE t
-- 计算通用折扣额X
CROSS APPLY (SELECT t.ACUCPRECIOBRUTOMON * t.EUR_RATE * t.ACUCDTOGEN / 100 AS X) calcX
-- 计算第一档折扣额Y（基于扣除X后的金额）
CROSS APPLY (SELECT (t.ACUCPRECIOBRUTOMON - calcX.X) * ISNULL(t.ACUCDTO1, 0) / 100 AS Y) calcY
-- 计算第二档折扣额Z（基于扣除X、Y后的金额）
CROSS APPLY (SELECT (t.ACUCPRECIOBRUTOMON - calcX.X - calcY.Y) * ISNULL(t.ACUCDTO2, 0) / 100 AS Z) calcZ
-- 计算第三档折扣额K（基于扣除X、Y、Z后的金额）
CROSS APPLY (SELECT (t.ACUCPRECIOBRUTOMON - calcX.X - calcY.Y - calcZ.Z) * ISNULL(t.ACUCDTO3, 0) / 100 AS K) calcK

2. 全数据库兼容方案

如果你的数据库不支持APPLY语法（比如低版本MySQL、其他不支持横向关联的数据库），用单层CTE替代多层嵌套子查询，同样仅扫描一次源表：

WITH CALCULO_INTERMEDIO AS (
  SELECT
    *,
    -- 所有中间值在同一层计算完成
    ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100 AS X,
    (ACUCPRECIOBRUTOMON - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)) * ISNULL(ACUCDTO1, 0) / 100 AS Y,
    (ACUCPRECIOBRUTOMON 
     - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)
     - ((ACUCPRECIOBRUTOMON - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)) * ISNULL(ACUCDTO1, 0) / 100)
    ) * ISNULL(ACUCDTO2, 0) / 100 AS Z,
    (ACUCPRECIOBRUTOMON 
     - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)
     - ((ACUCPRECIOBRUTOMON - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)) * ISNULL(ACUCDTO1, 0) / 100)
     - ((ACUCPRECIOBRUTOMON 
         - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)
         - ((ACUCPRECIOBRUTOMON - (ACUCPRECIOBRUTOMON * EUR_RATE * ACUCDTOGEN / 100)) * ISNULL(ACUCDTO1, 0) / 100)
        ) * ISNULL(ACUCDTO2, 0) / 100)
    ) * ISNULL(ACUCDTO3, 0) / 100 AS K
  FROM TABLE
)
SELECT 
  *,
  (ACUCPRECIOBRUTOMON * EUR_RATE) - X - Y - Z - K AS PRECIO_NETO_EUR
FROM CALCULO_INTERMEDIO

方案对比说明 #

• 原始多层嵌套子查询的写法，每一层嵌套都会触发一次TABLE的全量扫描，4层嵌套就会产生4次扫描开销，在大表场景下性能会明显下降
• 上述两种方案均只对TABLE做1次扫描，没有重复读取的开销
• 所有计算逻辑严格对齐你提供的原始子查询规则，没有修改任何折扣计算的基数、取数规则，不会产生业务计算偏差

注意：如果后续源表存在大量数据，建议不要保留多层嵌套的写法，优先选择上述单遍扫描的方案降低查询耗时。

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