如何在子查询中引用外部列，实现发票总金额的高效统计？

首先来看你的场景：你有两张关联表INVOICES和INVOICE_ITEMS，需要按照特定规则计算每张发票的总金额，而且因为数据库被其他应用占用，不能修改表中0和NULL的取值。

表结构与需求回顾 #

表结构 #

-- INVOICES表：存储发票基础信息
INVOICES
ID | DISCOUNT_PRC
1  | NULL
2  | 0.10
3  | 0.70
...

-- INVOICE_ITEMS表：存储发票明细
INVOICE_ITEMS
ID | INVOICE_ID | PRICE | ALT_PRICE
1  | 1          | 100   | 0
2  | 1          | 200   | 150
3  | 2          | 400   | 300
4  | 2          | 200   | 0
5  | 2          | 100   | NULL
6  | 3          | 200   | 40
7  | 3          | 100   | NULL
...

金额计算规则 #

• 如果ALT_PRICE不为NULL且大于0，直接取这个值作为该明细的金额
• 否则，用PRICE乘以折扣系数(1 - DISCOUNT_PRC)，注意DISCOUNT_PRC为NULL时按0处理（也就是不打折）

你已经写出了单条明细的正确查询，能得到每条商品的金额，但需要按发票汇总成一行，而且因为实际表有50+列还有其他子查询，不想在主查询里用SUM导致必须分组所有列。

你遇到的错误原因 #

你尝试用OUTER APPLY的写法报错，错误信息是：

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

这是因为SQL Server不允许在聚合函数（比如SUM）里同时引用外部表的字段（这里是I.DISCOUNT_PRC）和子查询内的其他字段（IT.ALT_PRICE、IT.PRICE）。另外你子查询里的group by IT.INVOICE_ID其实是多余的——已经用where IT.INVOICE_ID = I.ID过滤到单张发票的明细了，不需要再分组。

可行解决方案 #

方案1：子查询内关联（性能最优） #

这个方案是在汇总子查询内部重新关联INVOICES表，这样聚合函数里的所有引用都是子查询内部的，避开了外部引用的限制，同时主查询可以保留所有需要的列，不用分组：

select 
    I.ID,
    -- 子查询内部关联INVOICES，计算当前发票的总金额
    (
        select sum(
            case 
                when ISNULL(IT.ALT_PRICE, 0) > 0 then IT.ALT_PRICE 
                else IT.PRICE * (1 - ISNULL(I_inner.DISCOUNT_PRC, 0)) 
            end
        )
        from INVOICE_ITEMS IT
        join INVOICES I_inner on I_inner.ID = IT.INVOICE_ID
        where I_inner.ID = I.ID
    ) as OVERALL_PRICE
from INVOICES I

方案2：修复后的OUTER APPLY写法 #

如果坚持想用APPLY，去掉多余的group by就能解决错误：

select 
    I.ID, 
    items.OVERALL_PRICE 
FROM INVOICES I 
OUTER APPLY ( 
    select sum ( 
        case 
            when ISNULL(IT.ALT_PRICE, 0) > 0 then IT.ALT_PRICE 
            else IT.PRICE * (1 - ISNULL(I.DISCOUNT_PRC, 0)) 
        end
    ) AS OVERALL_PRICE 
    from INVOICE_ITEMS IT 
    where IT.INVOICE_ID = I.ID
) as items

不过这个方案的性能不如子查询内关联的版本。

方案3：OVER()窗口函数写法 #

用窗口函数也能实现，但需要用distinct去重，性能最差：

select distinct
    I.ID,
    sum(
        case 
            when ISNULL(IT.ALT_PRICE, 0) > 0 then IT.ALT_PRICE 
            else IT.PRICE * (1 - ISNULL(I.DISCOUNT_PRC, 0)) 
        end
    ) over (partition by I.ID) as OVERALL_PRICE
from INVOICES I
join INVOICE_ITEMS IT on IT.INVOICE_ID = I.ID

性能基准测试结果 #

我针对这三个方案做了测试：插入50k条发票和500k条明细，用MSSQL的STATISTICS TIME工具得到结果：

• 子查询内关联：

  CPU time = 1170 ms, elapsed time = 335 ms.
  CPU time = 1202 ms, elapsed time = 344 ms.
  CPU time = 1153 ms, elapsed time = 348 ms.
  

• OVER()窗口函数：

  CPU time = 3089 ms, elapsed time = 1361 ms.
  CPU time = 3010 ms, elapsed time = 1075 ms.
  CPU time = 3010 ms, elapsed time = 1070 ms.
  

• APPLY：

  CPU time = 2496 ms, elapsed time = 2320 ms.
  CPU time = 2433 ms, elapsed time = 2171 ms.
  CPU time = 2496 ms, elapsed time = 2179 ms.
  

结论 #

原本以为APPLY或者窗口函数会有更好的优化效果，但实际测试下来，子查询内关联的方案性能远超另外两个，这确实有点意外。之前我也担心内关联会拖慢性能，但实际效果很好。所以如果有类似的聚合需求，别犹豫在子查询里关联外部表来避开SQL的外部引用限制。

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