SQL ROLLUP与COALESCE
SQL ROLLUP与COALESCE
提问于 2021-11-12 07:12:27
数据：
CREATE TABLE [dbo].[Purchase_Items]
(
[ID] [int] NULL,
[Model_Name] [varchar](50) NULL,
[Brand] [varchar](50) NULL,
[Price] [decimal](18, 2) NULL,
[PurchaseDate] [date] NULL
);
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (1, N'Galaxy S10', N'Samsung', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-01-12' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (2, N'Galaxy S20', N'Samsung', CAST(55000.00 AS Decimal(18, 2)), CAST(N'2020-01-18' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (3, N'Galaxy Note10', N'Samsung', CAST(40000.00 AS Decimal(18, 2)), CAST(N'2020-01-22' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (4, N'Galaxy A51', N'Samsung', CAST(21000.00 AS Decimal(18, 2)), CAST(N'2020-01-31' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (5, N'iPhone X', N'Apple', CAST(50000.00 AS Decimal(18, 2)), CAST(N'2020-02-03' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (6, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-02-07' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (7, N'iPhone 6s', N'Apple', CAST(20000.00 AS Decimal(18, 2)), CAST(N'2020-02-10' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (8, N'iPhone 8', N'Apple', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-02-21' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (9, N'Y8', N'Huawei', CAST(16000.00 AS Decimal(18, 2)), CAST(N'2020-03-10' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (10, N'P30', N'Huawei', CAST(45000.00 AS Decimal(18, 2)), CAST(N'2020-03-12' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (11, N'Y9', N'Huawei', CAST(24000.00 AS Decimal(18, 2)), CAST(N'2020-03-14' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (12, N'P40', N'Huawei', CAST(50000.00 AS Decimal(18, 2)), CAST(N'2020-03-19' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (13, N'Redmi 9', N'Xiaomi', CAST(10000.00 AS Decimal(18, 2)), CAST(N'2020-03-21' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (14, N'Redmi K30', N'Xiaomi', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-04-03' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (15, N'Mi Note 10', N'Xiaomi', CAST(15000.00 AS Decimal(18, 2)), CAST(N'2020-04-21' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (16, N'BlackShark', N'Xiaomi', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-04-20' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (17, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-04-22' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (18, N'iPhone 11', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-05-03' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (19, N'Galaxy S20', N'Samsung', CAST(55000.00 AS Decimal(18, 2)), CAST(N'2020-05-10' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (20, N'Galaxy S10', N'Samsung', CAST(25000.00 AS Decimal(18, 2)), CAST(N'2020-05-12' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (21, N'Galaxy Note10', N'Samsung', CAST(40000.00 AS Decimal(18, 2)), CAST(N'2020-05-14' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (22, N'iPhone 8', N'Apple', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-05-21' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (23, N'BlackShark 3', N'Xiaomi', CAST(30000.00 AS Decimal(18, 2)), CAST(N'2020-05-22' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (24, N'P40', N'Huawei', CAST(45000.00 AS Decimal(18, 2)), CAST(N'2020-05-08' AS Date));
INSERT INTO [dbo].[Purchase_Items] ([ID], [Model_Name], [Brand], [Price], [PurchaseDate])
VALUES (25, N'iPhone X', N'Apple', CAST(70000.00 AS Decimal(18, 2)), CAST(N'2020-05-10' AS Date));查询：
SELECT
MONTH(PurchaseDate) AS Months,
COALESCE(Brand, 'Monthly Total') AS 'Brand',
SUM(Price) AS TotalAmount
FROM
Purchase_Items
GROUP BY
ROLLUP(PurchaseDate, Brand);我得到的输出如下：
它给每个单独的品牌项目没有分组的null，我希望最后一个null是‘总合计’而不是‘每月合计’。我尝试将PurchaseDate或品牌列放入和移出汇总，但似乎对要分组的品牌不起作用。
我想要的输出是这样的。计算每个品牌每月购买的所有商品，以及在我前面提到的所有日期购买的所有商品的总数
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-11-12 08:46:16
这可以使用CASE WHEN structure进行分组，如下所示：
SELECT
MONTH(PurchaseDate) AS Months,
CASE WHEN GROUPING(Brand) = 0 THEN Brand
When GROUPING(MONTH(PurchaseDate)) = 1 THEN 'Grand Total'
When GROUPING(Brand) = 1 THEN 'Monthly Total' END AS 'Brand',
SUM(Price) AS TotalAmount
FROM
Purchase_Items
GROUP BY MONTH(PurchaseDate), Brand WITH ROLLUP;Stack Overflow用户
发布于 2021-11-12 07:38:06
只需在汇总状态下使用MONTH(PurchaseDate)：
SELECT
MONTH(PurchaseDate) AS Months,
COALESCE(Brand, 'Monthly Total') AS 'Brand',
SUM(Price) AS TotalAmount
FROM
Purchase_Items
GROUP BY
ROLLUP(MONTH(PurchaseDate), Brand);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69939147
复制相关文章
相似问题
腾讯云开发者
