时间戳

首页 > 解决方案 > 根据与它同一行的另一列更改列中的字符串值

sql-server - 根据与它同一行的另一列更改列中的字符串值

问题描述

我想更改将基于具有同一行的另一列中的值的列中值的名称。

我正在使用 GROUP BY 运算符，使用 CUBE 关键字。

这是我用来创建表的代码：

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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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 [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) Month,
   CASE WHEN month(PurchaseDate) is null then CONCAT('Grand Total for ', Brand) 
          ELSE COALESCE(Brand, 'Monthly Subtotal') end AS Brand,
   SUM(Price) as 'Total Amount'
FROM Purchase_Items
GROUP BY CUBE(month(PurchaseDate), Brand)

这是输出：

Purchase Month   |         Brand           | Total Amount
---------------------------------------------------------
        3        |         Xiaomi          |   10000.00 
        4        |         Xiaomi          |   70000.00
        5        |         Xiaomi          |   30000.00
       NULL      |  Grand Total for Xiaomi |  110000.00
        2        |         Apple           |  170000.00
        4        |         Apple           |   70000.00
        5        |         Apple           |  170000.00
       NULL      |  Grand Total for Apple  |  410000.00           
       NULL      |    Grand Total for      |  520000.00
        1        |     Monthly Subtotal    |  250000.00
        2        |     Monthly Subtotal    |  200000.00
        3        |     Monthly Subtotal    |   70000.00

我想将“Grand Total for”更改为“Grand Total”，但我不知道如何操作。

我在想是否可以根据“总金额”列中的最高数字来更改它，因为它们共享同一行，但也许还有其他方法可以解决这个问题。

我怎样才能解决这个问题？

标签： sql-servergroup-bycase-whencoalesce

解决方案

测试类似的东西

CASE WHEN month(PurchaseDate) IS NULL AND Brand IS NULL THEN 'Grand Total'
     WHEN month(PurchaseDate) IS NULL                   THEN CONCAT('Grand Total for ', Brand) 
                                                        ELSE COALESCE(Brand, 'Monthly Subtotal') 
     END AS Brand,

在第一种情况下 - 也许WHEN Brand IS NULL就足够了？

推荐阅读