如何在HAVING子句中替换硬编码数值？基于AdventureWorks数据库

你的问题出在HAVING子句里的SUM(SubTotal)/17逻辑错误：这里的SUM(SubTotal)是当前分组（单个销售人员）的总销售额，除以17后肯定远小于该销售人员的实际总销售额，所以条件永远不成立，返回空表。

要动态引用全局的平均销售额，有两种常用方法：

方法一：使用子查询获取全局平均 #

直接在HAVING子句中嵌入计算全局平均的子查询，这样就能拿到所有销售人员的总销售额除以17的结果：

SELECT 
    SalesPersonID,
    SUM(SubTotal) AS TotalSalesBySalesPerson,
    FirstName + ' ' + LastName AS [Sales Person]
FROM Sales.SalesOrderHeader
JOIN Person.Person ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
WHERE SalesPersonID IS NOT NULL
GROUP BY SalesPersonID, FirstName, LastName
HAVING SUM(SubTotal) < (SELECT SUM(SubTotal)/17 FROM Sales.SalesOrderHeader WHERE SalesPersonID IS NOT NULL)
ORDER BY TotalSalesBySalesPerson DESC

方法二：使用CTE+窗口函数计算全局值 #

如果需要多次用到全局总销售额或平均值，用CTE（公共表表达式）结合窗口函数会更清晰：

WITH SalesSummary AS (
    SELECT 
        SalesPersonID,
        FirstName + ' ' + LastName AS [Sales Person],
        SUM(SubTotal) AS TotalSalesBySalesPerson,
        -- 用窗口函数计算所有销售人员的总销售额总和
        SUM(SUM(SubTotal)) OVER () AS GlobalTotalSales
    FROM Sales.SalesOrderHeader
    JOIN Person.Person ON SalesOrderHeader.SalesPersonID = Person.BusinessEntityID
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID, FirstName, LastName
)
SELECT 
    SalesPersonID,
    TotalSalesBySalesPerson,
    [Sales Person]
FROM SalesSummary
-- 基于全局总销售额计算平均值并筛选
WHERE TotalSalesBySalesPerson < GlobalTotalSales / 17
ORDER BY TotalSalesBySalesPerson DESC

这两种方法都能替代硬编码的数值，确保平均值是动态计算的，不会因为数据变化而失效。

内容的提问来源于stack exchange，提问作者Kervin Jean