SQL Server中如何显示Q1、Q5的零值？附Northwind聚类统计场景

要显示全部6个集群（包括无产品的Q1、Q5）的统计结果，核心思路是先构造包含所有集群的完整列表，再将统计结果与这个列表左连接，确保没有匹配数据的集群也能返回0值。

实现步骤： #

1. 用CTE（公共表表达式）定义所有6个集群的名称和对应的平均数量范围规则
2. 计算每个产品的平均订单数量，按集群规则分组统计产品数
3. 将集群列表与统计结果左连接，用ISNULL把空值替换为0

完整SQL代码： #

WITH ClusterDefinitions AS (
    SELECT 'Q1(<15)' AS ClusterName, 0 AS MinQty, 14 AS MaxQty UNION ALL
    SELECT 'Q2(15-20)' AS ClusterName, 15 AS MinQty, 20 AS MaxQty UNION ALL
    SELECT 'Q3(21-25)' AS ClusterName, 21 AS MinQty, 25 AS MaxQty UNION ALL
    SELECT 'Q4(26-30)' AS ClusterName, 26 AS MinQty, 30 AS MaxQty UNION ALL
    SELECT 'Q5(31-35)' AS ClusterName, 31 AS MinQty, 35 AS MaxQty UNION ALL
    SELECT 'Q6(>35)' AS ClusterName, 36 AS MinQty, 99999 AS MaxQty -- 用大数替代无限大
)
SELECT 
    cd.ClusterName,
    ISNULL(COUNT(prod_clusters.ProductID), 0) AS ProductCount
FROM ClusterDefinitions cd
LEFT JOIN (
    -- 子查询：计算每个产品的平均订单量并归类到对应集群
    SELECT 
        p.ProductID,
        CASE 
            WHEN AVG(od.Quantity) < 15 THEN 'Q1(<15)'
            WHEN AVG(od.Quantity) BETWEEN 15 AND 20 THEN 'Q2(15-20)'
            WHEN AVG(od.Quantity) BETWEEN 21 AND 25 THEN 'Q3(21-25)'
            WHEN AVG(od.Quantity) BETWEEN 26 AND 30 THEN 'Q4(26-30)'
            WHEN AVG(od.Quantity) BETWEEN 31 AND 35 THEN 'Q5(31-35)'
            WHEN AVG(od.Quantity) > 35 THEN 'Q6(>35)'
        END AS ClusterName
    FROM Products p
    JOIN [Order Details] od ON p.ProductID = od.ProductID
    GROUP BY p.ProductID
) prod_clusters ON cd.ClusterName = prod_clusters.ClusterName
GROUP BY cd.ClusterName
ORDER BY 
    -- 按集群顺序排序
    CASE cd.ClusterName
        WHEN 'Q1(<15)' THEN 1
        WHEN 'Q2(15-20)' THEN 2
        WHEN 'Q3(21-25)' THEN 3
        WHEN 'Q4(26-30)' THEN 4
        WHEN 'Q5(31-35)' THEN 5
        WHEN 'Q6(>35)' THEN 6
    END;

代码说明： #

• ClusterDefinitions CTE：提前定义所有集群的名称和数量范围，确保每个集群都能出现在结果中
• 左连接LEFT JOIN：保证即使某个集群没有匹配产品，也会被保留在结果集里
• ISNULL(COUNT(...), 0)：将无产品集群的空统计值替换为0，直观展示该集群的产品数量

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