T-SQL：如何為從同一個表中選擇的不同日期范圍回傳單獨的資料列？

我試圖從訂單表中按范圍獲取客戶支出資料，但無論我嘗試指定日期范圍，腳本似乎總是將每個客戶 ID 的所有表行組合在一起。非常感謝任何有關我如何在這里實作目標的指導。

前任：

CREATE TABLE customers 
(
    idCustomer INT IDENTITY(100,1),
    name VARCHAR(100),
    PRIMARY KEY (idCustomer)
);

INSERT INTO customers 
VALUES ('Bob'), ('Barb');

CREATE TABLE orders 
(
    idOrder INT IDENTITY(1,1),
    idCustomer INT,
    orderTotal DECIMAL(19,2),
    orderDate DATETIME2
    PRIMARY KEY (idOrder)
);

INSERT INTO orders 
VALUES (100, 25.25, '2018-4-15'),
       (100, 37.00, '2018-6-15'),
       (100, 175.00, '2019-3-1'),
       (100, 232.33, '2019-8-3'),
       (101, 18.56, '2018-1-17'),
       (101, 3004.50, '2018-5-12'),
       (101, 2.98, '2019-6-1'),
       (101, 15.00, '2019-11-3')

SELECT
    c.idCustomer,
    c.name,
    COUNT(ord2018.idOrder) AS '#Orders 2018',
    SUM(ord2018.orderTotal) AS 'Total Spent 2018'
    COUNT(ord2019.idOrder) AS '#Orders 2019',
    SUM(ord2019.orderTotal) AS 'Total Spent 2019'
    COUNT(ordersAll.idOrder) AS '#Orders Lifetime',
    SUM(ordersAll.orderTotal) AS 'Total Spent Lifetime'
FROM 
    customers c
JOIN 
    orders ord2018 ON ord2018.idCustomer = c.idCustomer 
                   AND ord2018.orderDate > '2017-12-31'
                   AND ord2018.orderDate < '2019-1-1'
JOIN 
    orders ord2019 ON ord2019.idCustomer = c.idCustomer
                   AND ord2019.orderDate > '2018-12-31'
                   AND ord2019.orderDate < '2020-1-1'
JOIN
    orders ordersAll on ordersAll.idCustomer = c.idCustomer
GROUP BY 
    c.idCustomer, c.name

我想看到這樣的東西：

但是我在總列中得到重復值，這似乎只是每個客戶訂單表中所有記錄的總和。

提前致謝。

uj5u.com熱心網友回復：

對于已定義的問題以及您從硬編碼的示例資料中了解的年份：

;WITH agg AS
(
  SELECT idCustomer, y = YEAR(orderDate), 
    OrderCount = COUNT(*), 
    TotalSpent = COALESCE(SUM(orderTotal),0)
  FROM dbo.orders
  GROUP BY idCustomer, DATEPART(YEAR, orderDate)
)
SELECT agg.idCustomer, c.name,
  OrderCount2018 = MAX(CASE WHEN y = 2018 THEN OrderCount END),
  TotalSpent2018 = MAX(CASE WHEN y = 2018 THEN TotalSpent END),
  OrderCount2019 = MAX(CASE WHEN y = 2019 THEN OrderCount END),
  TotalSpent2019 = MAX(CASE WHEN y = 2019 THEN TotalSpent END),
  LifetimeCount  = SUM(OrderCount),
  LifetimeSpent  = SUM(TotalSpent)
FROM agg
INNER JOIN dbo.customers AS c
  ON c.idCustomer = agg.idCustomer
GROUP BY agg.idCustomer, c.name;

但是，您希望查詢是動態的，因此您不能硬編碼年份和列名。要動態執行此操作：

DECLARE @MinYear int, @MaxYear int;

SELECT @MinYear = MIN(YEAR(orderDate)), @MaxYear = MAX(YEAR(orderDate))
  FROM dbo.orders;
  
DECLARE @sql nvarchar(max) = N';WITH agg AS
(
  SELECT idCustomer, y = YEAR(orderDate), 
    OrderCount = COUNT(*), 
    TotalSpent = COALESCE(SUM(orderTotal),0)
  FROM dbo.orders
  GROUP BY idCustomer, DATEPART(YEAR, orderDate)
)
SELECT agg.idCustomer, c.name';

;WITH y(y) AS (SELECT @MinYear UNION ALL
  SELECT y 1 FROM y WHERE y < @MaxYear),
  z(y) AS (SELECT CONVERT(char(4), y) FROM y)
 SELECT @sql  = N',
  OrderCount'   y   N' = MAX(CASE WHEN y = '   y   N' THEN OrderCount END),
  TotalSpent'   y   N' = MAX(CASE WHEN y = '   y   N' THEN TotalSpent END)'
 FROM z;
  
SET @sql  = N',
  LifetimeCount  = SUM(OrderCount),
  LifetimeSpent  = SUM(TotalSpent)
FROM agg
INNER JOIN dbo.customers AS c
  ON c.idCustomer = agg.idCustomer
GROUP BY agg.idCustomer, c.name;';

SELECT @sql;
EXEC sys.sp_executesql @sql;

兩種情況下的輸出：

• 示例db<>fiddle

uj5u.com熱心網友回復：

對于您提供的示例資料并考慮您的示例查詢嘗試，您可以將條件總和與apply結合使用。

select * 
from customers c
outer apply (
    select 
      IsNull(Sum(case when Year(orderdate) = 2018 then 1 end),0) [#Orders2018],
      IsNull(Sum(case when Year(orderdate) = 2018 then ordertotal end),0) [Total spent 2018],
      IsNull(Sum(case when Year(orderdate) = 2019 then 1 end),0) [#Orders2019],
      IsNull(Sum(case when Year(orderdate) = 2019 then ordertotal end),0) [Total spent 2019],
      Count(*) TotalOrders,
      Sum(Ordertotal) TotalSpend
    from orders o
    where o.idCustomer = c.idCustomer
)o;

示例 DB<>Fiddle

• 為了完整起見，編輯，添加了總計:)

標籤：sql sql-server-2016

上一篇：如果SQL中的行為空，則聯合

下一篇：如何使用PDOPHP計算SQLite3中受影響的行數？