求订单量Top3城市的最受欢迎产品分类SQL语句优化问询

你的问题出在最终按City和ProductCategory.Name分组，导致每个城市会返回所有有交易的产品分类。要实现每个城市仅返回最受欢迎的分类，需要用窗口函数对每个城市内的分类进行排名，再筛选出排名第一的结果。

步骤说明 #

1. 获取订单量Top3的城市：先统计每个城市的订单总数（按SalesOrderID去重，避免因订单明细多导致统计重复），取前3名。
2. 计算城市-分类的交易数：关联所有表，统计每个城市下每个产品分类的交易次数（这里按订单明细数统计，若要按订单数则用COUNT(DISTINCT SalesOrderID)）。
3. 给分类排名：用ROW_NUMBER()窗口函数，按城市分组，按分类的交易数降序排序，给每个分类分配排名。
4. 筛选Top1分类：只保留每个城市中排名为1的分类。

完整SQL代码 #

WITH Top3Cities AS (
    -- 获取订单量Top3的城市
    SELECT TOP 3 
        a.City,
        COUNT(DISTINCT soh.SalesOrderID) AS TotalOrders
    FROM SalesLT.SalesOrderHeader soh
    JOIN SalesLT.Address a ON soh.ShipToAddressID = a.AddressID
    GROUP BY a.City
    ORDER BY TotalOrders DESC
),
CityCategoryStats AS (
    -- 统计每个Top3城市下各产品分类的交易数并排名
    SELECT 
        t.City,
        pc.Name AS CategoryName,
        COUNT(sod.SalesOrderDetailID) AS CategoryOrderCount, -- 按订单明细数统计
        -- 若要按订单数统计，替换为COUNT(DISTINCT sod.SalesOrderID)
        ROW_NUMBER() OVER (PARTITION BY t.City ORDER BY COUNT(sod.SalesOrderDetailID) DESC) AS Rank
    FROM Top3Cities t
    JOIN SalesLT.Address a ON a.City = t.City
    JOIN SalesLT.SalesOrderHeader soh ON soh.ShipToAddressID = a.AddressID
    JOIN SalesLT.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN SalesLT.Product p ON sod.ProductID = p.ProductID
    JOIN SalesLT.ProductCategory pc ON p.ProductCategoryID = pc.ProductCategoryID
    GROUP BY t.City, pc.Name
)
-- 筛选每个城市排名第一的分类
SELECT 
    City,
    CategoryName AS MostPopularCategory,
    CategoryOrderCount,
    (SELECT TotalOrders FROM Top3Cities WHERE City = c.City) AS CityTotalOrders
FROM CityCategoryStats c
WHERE Rank = 1
ORDER BY CityTotalOrders DESC;

关键说明 #

• ROW_NUMBER()：确保每个城市内只有一个排名第一的分类（如果有多个分类交易数相同，只会取其中一个；若要返回并列第一，改用RANK()）。
• CTE（公共表表达式）：将逻辑拆分为多个步骤，更易读和维护。
• 关联表时通过城市匹配地址ID，避免同一城市多个地址ID导致的重复统计。

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