-- 錯誤: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(...)))。
-- 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();
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 是對 **分組後** 結果的過濾,必須配合 GROUP BY(隱式或顯式)。GROUP BY 時,整張表視為一組,HAVING 等價於 WHERE 但可用聚合函數。WHERE 過濾行(分組前),HAVING 過濾組(分組後),**不可互換**。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;