按多列值自定义命令Union查询

Question

我有一个包含项目销售数据的表，如下所示：

Item_Category || Item_Subcategory || Item_ID        || No_of_Sales
Produce       || Fruits           || Mango          || 13
Produce       || Fruits           || Apple          || 21
Produce       || Fruits           || Banana         || 15
Produce       || Fruits           || Strawberry     || 19
Produce       || Vegetables       || Onion          || 7
Produce       || Vegetables       || Potato         || 41
Produce       || Vegetables       || Carrot         || 32
Clothing      || Formal           || Suits          || 8
Clothing      || Formal           || Trousers       || 46
Clothing      || Formal           || Shirts         || 16
Clothing      || Partywear        || Designer Suits || 4
Clothing      || Casual           || Tees           || 11
Clothing      || Casual           || Jeans          || 23
Clothing      || Casual           || Jackets        || 17
Clothing      || Casual           || Turtlenecks    || 33
Clothing      || Casual           || Belts          || 45

我需要在创建UNION查询的同一列中显示每个项目类别的总销售额以及每个Item_Subcategory

SELECT [Item_Category],
       [Item_Subcategory] AS [Item_Type],
       SUM([No_Of_Sales]) [Number of Sales]
FROM MyDB.dbo.Item_Sales_Data
GROUP BY [Item_Category],[Item_Subcategory]
UNION ALL
SELECT [Item_Category],
       [Item_Category],
       SUM([No_Of_Sales])
FROM MyDB.dbo.Item_Sales_Data
GROUP BY [Item_Category]
ORDER BY [Item_Category],[Item_Subcategory];

不幸的是，我得到的输出如下：

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

预期产出如下：

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

我怎样才能做到这一点？

Answer 1

您可以添加一个用于排序的人工列--尝试如下：

SELECT [Item_Category], [Item_Type],[Number of Sales]
FROM (
    SELECT [Item_Category],
           [Item_Subcategory] AS [Item_Type],
           SUM([No_Of_Sales]) [Number of Sales],
           0 RN
    FROM dbo.Item_Sales_Data
    GROUP BY [Item_Category],[Item_Subcategory]
    UNION ALL
    SELECT [Item_Category],
           [Item_Category],
           SUM([No_Of_Sales]),
           -1 RN
    FROM dbo.Item_Sales_Data
    GROUP BY [Item_Category]
) a
ORDER BY [Item_Category] desc, RN,[Item_Type];