萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 聚合函數嵌套限制與 HAVING 進階:以平均銷售額分析為例

問題:不能直接嵌套聚合

-- 錯誤:Msg 130, Level 15
SELECT SalesOrderID, AVG(SUM(LineTotal)) AS AvgSale
FROM Sales.SalesOrderHeader H
JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
GROUP BY H.SalesOrderID;

SQL 標準禁止在同一層級嵌套聚合函數(AVG(SUM(...)))。

解法:分解為兩步(UDF / CTE / 子查詢)

方案一:表值函數 (TVF) + 標量函數

-- 1. 計算每單總額
CREATE FUNCTION dbo.SalesTotals_TV() RETURNS TABLE
AS RETURN
SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID;

-- 2. 計算平均值
CREATE FUNCTION dbo.AvgSale_Scalar() RETURNS MONEY
AS BEGIN
    DECLARE @Avg MONEY;
    SELECT @Avg = AVG(TotalSale) FROM dbo.SalesTotals_TV();
    RETURN @Avg;
END;

-- 3. 組合輸出
DECLARE @Avg MONEY = dbo.AvgSale_Scalar();
SELECT *, @Avg AS Average, TotalSale/@Avg AS Ratio,
    CASE WHEN TotalSale/@Avg > 1 THEN 'Above Avg'
         WHEN TotalSale/@Avg < 1 THEN 'Below Avg'
         ELSE 'Average' END AS Level
FROM dbo.SalesTotals_TV();

方案二:CTE 鏈式聚合(無對象依賴,推薦臨時分析)

WITH OrderTotals AS (
    SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
    FROM Sales.SalesOrderDetail
    GROUP BY SalesOrderID
), AvgCalc AS (
    SELECT AVG(TotalSale) AS AvgSale FROM OrderTotals
)
SELECT OT.*, AC.AvgSale,
    OT.TotalSale / AC.AvgSale AS Ratio,
    CASE WHEN OT.TotalSale > AC.AvgSale THEN 'Above'
         WHEN OT.TotalSale < AC.AvgSale THEN 'Below'
         ELSE 'Avg' END AS Level
FROM OrderTotals OT CROSS JOIN AvgCalc AC;

HAVING 子句本質

  • HAVING 是對 **分組後** 結果的過濾,必須配合 GROUP BY(隱式或顯式)。
  • 無 GROUP BY 時,整張表視為一組,HAVING 等價於 WHERE 但可用聚合函數。
  • WHERE 過濾行(分組前),HAVING 過濾組(分組後),**不可互換**。

實戰模板:客戶訂單總額超 25000 篩選

SELECT C.CustomerID, H.SalesOrderID, SUM(D.LineTotal) AS SubTotal
FROM Sales.Customer C
JOIN Sales.SalesOrderHeader H ON H.CustomerID = C.CustomerID
JOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderID
GROUP BY C.CustomerID, H.SalesOrderID
HAVING SUM(D.LineTotal) > 25000
ORDER BY C.CustomerID, H.SalesOrderID;
copyright © 萬盛學電腦網 all rights reserved