在SQL SERVER中获取Sales Commoditywise

Question

我想介绍一下今年的商品销售情况。但是，应该显示所有商品，但是显示属于特定年份的商品的销售额，而其余商品只是显示空值，但是年份应该是选定的年份。

我有下面的数据。

Declare @table table (Commodity Varchar(10),Sales int,YearOfSale INT)
INSERT INTO @Table
Values('Samsung',300,2020)
,('OPPO',500,2020)
,('NOKIA',700,2020)
,('OPPO',1000,2020)
,('REDMI',100,2029)
,('KORBON',90,2019)

我想要下面的结果。

Commodity   TOTALSALES  YearOfSale
----------------------------------
OPPO            1500    2020
NOKIA           700     2020
Samsung         300     2020
REDMI           NULL    2020
KORBON          NULL    2020

我试过下面的方法

;WITH Commodity
AS (
    SELECT DISTINCT Commodity
        ,YearOfSale
    FROM @Table
    )
    ,Sales
AS (
    SELECT Commodity
        ,SUM(Sales) Sales
        ,YearOfSale
    FROM @Table
    WHERE YearOfsale=2020
    GROUP BY Commodity
        ,YEAROfsale
    )
SELECT P.Commodity
    ,CASE 
                WHEN Sales IS NOT NULL
                    THEN Sales 
                END TOTALSALES
    ,P.YearOfSale
FROM Commodity P
LEFT JOIN Sales QS ON P.Commodity = QS.Commodity
ORDER BY Sales DESC

我得到了2019年，但我需要介绍2020年。如果能帮上点忙我会很感激的。

Answer 1

由于您只对2020年的销售感兴趣，因此可以在结果列中对其进行硬编码。并且您不需要在Commodity cte中使用YearOfSale

;WITH Commodity
AS (
    SELECT DISTINCT Commodity
    FROM @Table
    )
    ,Sales
AS (
    SELECT Commodity
        ,SUM(Sales) Sales
        ,YearOfSale
    FROM @Table
    WHERE YearOfSale=2020
    GROUP BY Commodity
        ,YearOfSale
    )
SELECT P.Commodity
    ,Sales
    ,YearOfSale = 2020
FROM Commodity P
LEFT JOIN Sales QS ON P.Commodity = QS.Commodity
ORDER BY Sales DESC

Answer 2

这里的模式是对年份和商品进行CROSS JOIN，为每个组合创建一个行，然后对销售额进行LEFT JOIN，以填充具有实际销售额的(Commodity，Year)对的销售额。例如

SELECT P.Commodity, Y.YearOfSale, Sum(S.Sales) TotalSales
FROM Commodity P
CROSS JOIN Years Y
LEFT JOIN @table  S 
  ON P.Commodity = S.Commodity
 AND Y.YearOfSale = S.YearOfSale 
GROUP BY P.Commodity, Y.YearOfSale
ORDER BY TotalSales DESC

请注意，您可能需要为年份和商品创建查找表，如果您还没有这些表的话。或者，您可以从sales表中派生出这些内容，例如

Declare @table table (Commodity Varchar(10),Sales int,YearOfSale INT)
INSERT INTO @Table
Values('Samsung',300,2020)
,('OPPO',500,2020)
,('NOKIA',700,2020)
,('OPPO',1000,2020)
,('REDMI',100,2029)
,('KORBON',90,2019);


with 
  Commodity as (select distinct Commodity from @table),
  Years as (select distinct YearOfSale from @table)
SELECT P.Commodity, Y.YearOfSale, Sum(S.Sales) TotalSales
FROM Commodity P
CROSS JOIN Years Y
LEFT JOIN @table  S 
  ON P.Commodity = S.Commodity
 AND Y.YearOfSale = S.YearOfSale 
GROUP BY P.Commodity, Y.YearOfSale
ORDER BY TotalSales DESC

输出

Commodity  YearOfSale  TotalSales
---------- ----------- -----------
OPPO       2020        1500
NOKIA      2020        700
Samsung    2020        300
REDMI      2029        100
KORBON     2019        90
NOKIA      2019        NULL
OPPO       2019        NULL
REDMI      2019        NULL
Samsung    2019        NULL
KORBON     2020        NULL
KORBON     2029        NULL
NOKIA      2029        NULL
OPPO       2029        NULL
Samsung    2029        NULL
REDMI      2020        NULL