sql - 使用 COALESCE 的 SQL ROLLUP
问题描述
数据:
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 或 Brand 列放入和取出 ROLLUP,但对于要分组的 Brand 似乎不起作用。
我想要的输出是这样的。计算每个品牌和每月购买的所有商品,以及我之前提到的所有日期中所有购买商品的总和
解决方案
只需在 ROLLUP 状态下使用 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);
推荐阅读
- python - 尝试获取用户的警告数量并将其发送 discord.py
- java - 如何确保在 Android Studio/Java 中不会多次调用导航操作?
- javascript - Reactstrap 工具提示:无法设置动态 id 和目标:SyntaxError:无法在“文档”上执行“querySelectorAll”:“5f54e5d61ec2c635f81f0e6a”
- javascript - 带有 javascript 调试的 Visual Studio 在直接运行和在调试模式下运行时显示不同的行为
- python - QTreeView 中的列对齐 | PyQt5
- php - 表单无法将数据插入我的数据库
- android - DataOutputStream.writeBytes,同时打开了太多套接字文件
- javascript - 这两种状态更新方法有区别吗?
- javascript - 与节点中的 object.value 一起使用时,“in”关键字无法正常工作
- html - 具有最小高度的网格内部的最大高度图像应该尊重网格的大小而不是扩展它