如何按多列排序UNION查询以展示分类与子分类销售总额

你已经写出了核心的汇总逻辑，问题就出在排序环节上！原查询的ORDER BY [Item_Category],[Item_Subcategory]是按字符串自然排序，而大类汇总行的Item_Type是大类名称（比如Clothing），子分类名称（比如Casual）在字典序上比大类名称靠前，所以会出现子分类行排在大类汇总行前面的情况。

要实现大类汇总行排在对应分类的所有子分类行之前的效果，我们可以给两类行加个排序优先级标记，让汇总行先显示，再按子分类排序。

修改后的查询（带排序标识） #

-- 子分类汇总：标记优先级为2，让它排在大类汇总之后
SELECT 
    [Item_Category], 
    [Item_Subcategory] AS [Item_Type], 
    SUM([No_Of_Sales]) AS [Number of Sales],
    2 AS Sort_Priority
FROM MyDB.dbo.Item_Sales_Data
GROUP BY [Item_Category],[Item_Subcategory]

UNION ALL

-- 大类汇总：标记优先级为1，让它排在最前面
SELECT 
    [Item_Category], 
    [Item_Category] AS [Item_Type], 
    SUM([No_Of_Sales]) AS [Number of Sales],
    1 AS Sort_Priority
FROM MyDB.dbo.Item_Sales_Data
GROUP BY [Item_Category]

-- 排序逻辑：先按分类，再按优先级（1在前），最后按子分类
ORDER BY 
    [Item_Category], 
    Sort_Priority, 
    [Item_Subcategory];

隐藏排序标识的写法 #

如果不想在结果里看到Sort_Priority列，可以把查询嵌套起来，在外层只返回需要的列：

SELECT [Item_Category], [Item_Type], [Number of Sales]
FROM (
    SELECT 
        [Item_Category], 
        [Item_Subcategory] AS [Item_Type], 
        SUM([No_Of_Sales]) AS [Number of Sales],
        2 AS Sort_Priority
    FROM MyDB.dbo.Item_Sales_Data
    GROUP BY [Item_Category],[Item_Subcategory]

    UNION ALL

    SELECT 
        [Item_Category], 
        [Item_Category] AS [Item_Type], 
        SUM([No_Of_Sales]) AS [Number of Sales],
        1 AS Sort_Priority
    FROM MyDB.dbo.Item_Sales_Data
    GROUP BY [Item_Category]
) AS CombinedResults
ORDER BY 
    [Item_Category], 
    Sort_Priority, 
    [Item_Type];

最终效果 #

这样调整后，就能得到你期望的输出：

Item_Category || Item_Type || Number of Sales
Produce || Produce || 148
Produce || Fruits || 68
Produce || Vegetables || 80
Clothing || Clothing || 203
Clothing || Casual || 129
Clothing || Formal || 70
Clothing || Partywear || 4

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