SQL Server 計算列與平均值進階：HAVING 子查詢、巢狀聚合限制與表值函數拆解

SQL Server 計算列與平均值進階：HAVING 子查詢、巢狀聚合限制與表值函數拆解

SQL Server 不允許在同一查詢中疊加兩層聚合（如 AVG(SUM(...))），會報錯 Msg 130: Cannot perform an aggregate function on an expression containing an aggregate。本文演示如何用表值函數（TVF）拆解：先算小計，再算平均，最後關聯輸出比率與等級。

場景：訂單金額超 25,000 客戶清單

CREATE FUNCTION dbo.SalesTotals_TVF()
RETURNS TABLE
AS RETURN (
  SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
  FROM Sales.SalesOrderDetail
  GROUP BY SalesOrderID
);

步驟 2：標量函數計算平均值

DECLARE @Avg MONEY = dbo.AverageSale_Scalar();
SELECT
  st.SalesOrderID,
  st.TotalSale,
  @Avg AS AverageSale,
  st.TotalSale / @Avg AS Ratio,
  CASE
    WHEN st.TotalSale / @Avg > 1 THEN 'Above Average'
    WHEN st.TotalSale / @Avg < 1 THEN 'Below Average'
    ELSE 'Average'
  END AS Level
FROM dbo.SalesTotals_TVF() st;

核心原則

• 同一層級不可疊加聚合，需拆分為多個函數/CTE/子查詢。
• 表值函數（RETURNS TABLE）可直接 JOIN，效能優於標量函數逐行調用。
• HAVING 適用於分組後過濾，必須配合 GROUP BY；無分組時用 WHERE。