sql - SQL 将每个月的平均值并排放在列中

Question

我有两个查询计算旧折扣和新折扣之间的平均值。我正在计算每月的平均值，我想知道如何将这些值并排放置：

这是我的查询：

Select AVG(ABS(NewDiscount - OldDiscount)) AS [Average Discount Change in February]
From DiscountChange
where MONTH(ChangeDate) = 2
and YEAR(ChangeDate) = 2021

Select AVG(ABS(NewDiscount - OldDiscount)) AS [Average Discount Change in January]
From DiscountChange
where MONTH(ChangeDate) = 1
and YEAR(ChangeDate) = 2021

我想得到的输出是：

[Average Discount Change in February] | [Average Discount Change in January]

这是 DiscountChange 表的架构：

Create Table DiscountChange
(
    DiscountChangeId Decimal(12) NOT NULL PRIMARY KEY
    , CouponId Decimal(12) NOT NULL FOREIGN KEY REFERENCES Coupon(CouponId)
    , OldDiscount MONEY NOT NULL
    , NewDiscount MONEY NOT NULL
    , ChangeDate DATE NOT NULL 
)

如何修改我的查询以实现上述输出？

提前致谢

标签： sqlsql-server

Answer 1

您可以使用条件聚合：

Select AVG(CASE WHEN MONTH(ChangeDate) = 1 THEN ABS(NewDiscount - OldDiscount) END),
       AVG(CASE WHEN MONTH(ChangeDate) = 2 THEN ABS(NewDiscount - OldDiscount) END)      
From DiscountChange
where ChangeDate >= '2021-01-01' AND
      ChangeDate < '2021-03-01'