場景

查詢訂單總額超過 25,000 的客戶，需連接 Customer、SalesOrderHeader、SalesOrderDetail 三表。

基本 HAVING 用法

SELECT 
  c.CustomerID, 
  soh.SalesOrderID, 
  SUM(sod.LineTotal) AS SubTotal
FROM Sales.Customer c
JOIN Sales.SalesOrderHeader soh ON soh.CustomerID = c.CustomerID
JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID
GROUP BY c.CustomerID, soh.SalesOrderID
HAVING SUM(sod.LineTotal) > 25000.00
ORDER BY c.CustomerID, soh.SalesOrderID;

陷阱：無法在同一層級雙重彙總

-- 錯誤：Cannot perform an aggregate function on an expression containing an aggregate
SELECT SalesOrderID, AVG(SUM(LineTotal)) FROM ... GROUP BY SalesOrderID;

解決方案：分解為表值函數 + 標量函數

-- 1. 表值函數：各訂單總額
CREATE FUNCTION dbo.SalesTotals_fnt() RETURNS TABLE AS RETURN
(SELECT SalesOrderID, SUM(LineTotal) AS TotalSale
 FROM Sales.SalesOrderDetail GROUP BY SalesOrderID);

-- 2. 標量函數：平均訂單額
CREATE FUNCTION dbo.AverageSale_fns() RETURNS MONEY AS
BEGIN
  DECLARE @Result MONEY;
  SELECT @Result = AVG(TotalSale) FROM dbo.SalesTotals_fnt();
  RETURN @Result;
END;

-- 3. 組合查詢：各訂單與平均值比較
DECLARE @Avg MONEY = dbo.AverageSale_fns();
SELECT *, @Avg AS Average, TotalSale/@Avg AS Ratio,
  CASE WHEN TotalSale/@Avg > 1 THEN 'Above Average'
       WHEN TotalSale/@Avg < 1 THEN 'Below Average'
       ELSE 'Average' END AS Level
FROM dbo.SalesTotals_fnt();

原則

• HAVING 用於群組篩選，需至少一個彙總函數。
• 同一 SELECT 層級不可巢狀彙總 (AVG(SUM(...)))，需拆分為多步驟或函數。
• 表值函數可複用，效能優於臨時表/子查詢。