SQL Server HAVING 子句進階：聚合過濾與多層聚合拆解技巧

HAVING 專為「聚合結果過濾」設計，必配合 GROUP BY 使用。單表場景下 WHERE 即可，HAVING 核心價值在於多表連接後的聚合過濾。

經典場景：訂單總額超 25000 美元的客戶

SELECT C.CustomerID, OH.SalesOrderID, SUM(OD.LineTotal) AS SubTotal
FROM Sales.Customer C
JOIN Sales.SalesOrderHeader OH ON OH.CustomerID = C.CustomerID
JOIN Sales.SalesOrderDetail OD ON OD.SalesOrderID = OH.SalesOrderID
GROUP BY C.CustomerID, OH.SalesOrderID
HAVING SUM(OD.LineTotal) > 25000
ORDER BY C.CustomerID, OH.SalesOrderID

陷阱：無法同層雙聚合

-- 錯誤：Cannot perform an aggregate function on an expression containing an aggregate
SELECT OH.SalesOrderID, AVG(SUM(OD.LineTotal)) AS Average
FROM Sales.SalesOrderHeader OH
JOIN Sales.SalesOrderDetail OD ON OD.SalesOrderID = OH.SalesOrderID
GROUP BY OH.SalesOrderID

解法：拆解為表值函數 + 標量函數組合

-- 1. 表值函數：訂單級聚合
CREATE FUNCTION dbo.SalesTotals()
RETURNS TABLE
AS RETURN (
  SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
  FROM Sales.SalesOrderDetail
  GROUP BY SalesOrderID
)

-- 2. 標量函數：全局平均
CREATE FUNCTION dbo.AverageSale()
RETURNS MONEY
AS BEGIN
  DECLARE @Avg MONEY
  SELECT @Avg = AVG(TotalSale) FROM dbo.SalesTotals()
  RETURN @Avg
END

-- 3. 組合查詢：單訂單 vs 平均值
SELECT *, dbo.AverageSale() AS Average,
  TotalSale / dbo.AverageSale() AS Ratio,
  CASE WHEN TotalSale / dbo.AverageSale() > 1 THEN 'Above'
       WHEN TotalSale / dbo.AverageSale() < 1 THEN 'Below'
       ELSE 'Average' END AS Level
FROM dbo.SalesTotals()

核心原則

1. HAVING 只能過濾同層聚合結果
2. 需跨層聚合（如「訂單平均值」→「所有訂單的平均」）時，拆分為獨立函數/視圖/CTE 再組合
3. 表值函數可內聯優化，性能損失可控