如果没有可用的未来日期，SQL Server将选择最近的过去日期

I have a table structure as below,

我有一个表格结构如下所示，

CREATE TABLE #CustOrder ( CustId INT, OrderDate DATE )

INSERT #CustOrder ( CustId, OrderDate )
VALUES  ( 1, '2016-11-01' ),
        ( 1, '2019-09-01' ),
        ( 2, '2019-07-01' ),
        ( 2, '2019-11-01' ),
        ( 3, '2017-01-01' ),
        ( 4, '2016-12-01' ),
        ( 4, '2017-01-01' )

I want to list the customer with their future order dates, if they do not have a future order I want to list their last or most recent order. I have the following query.

我想要列出他们未来的订单日期，如果他们没有未来的订单，我想列出他们最近的订单。我有以下查询。

; WITH LastOrder AS
(
    SELECT 
        CO.CustId, 
        CO.OrderDate, 
        ROW_NUMBER() OVER(PARTITION BY CO.CustId ORDER BY ABS(DATEDIFF(DAY, CO.OrderDate, GETUTCDATE()))) AS RowNum 
    FROM #CustOrder AS CO

)
SELECT LO.CustId, LO.OrderDate
FROM LastOrder AS LO
WHERE LO.RowNum = 1

This query gives me the result as,

这个查询得到的结果是，

CustId  |   OrderDate
--------+-------------
1       |   2016-11-01
2       |   2019-07-01
3       |   2017-01-01
4       |   2017-01-01

However, I need the result as,

然而，我需要的结果是，

CustId  |   OrderDate
--------+-------------
1       |   2019-09-01
2       |   2019-07-01
3       |   2017-01-01
4       |   2017-01-01

作为

Customer 1 has a future order on 2019-09-01
客户1有一个2019-09-01的未来订单。
Customer 2 has two future order but the first one is on 2019-07-01
客户2有两个未来的订单，但是第一个订单是2019-07-01
Customer 3 has no more than 1 order, it should just return 2017-01-01
客户3的订单不超过1个，应该是2017-01-01
Customer 4 has two past orders but the most recent is 2017-01-01
客户4有两个过去的订单，但最近的是2017-01-01

5 个解决方案

#1

rextester: http://rextester.com/PBKNA95127

rextester:http://rextester.com/PBKNA95127

CREATE TABLE #CustOrder ( CustId INT, OrderDate DATE )

INSERT #CustOrder ( CustId, OrderDate )
VALUES  ( 1, '2016-11-01' ),
        ( 1, '2019-09-01' ),
        ( 2, '2019-07-01' ),
        ( 2, '2019-11-01' ),
        ( 3, '2017-01-01' ),
        ( 4, '2016-12-01' ),
        ( 4, '2017-01-01' )

; WITH LastOrder AS
(
    SELECT 
        CO.CustId, 
        CO.OrderDate, 
        ROW_NUMBER() OVER(PARTITION BY CO.CustId 
                          ORDER BY case when co.OrderDate > getdate() then 0 else 1 end
                          , abs(DATEDIFF(DAY, getdate(),CO.OrderDate)) asc
                         ) AS RowNum 
    FROM #CustOrder AS CO

)
SELECT LO.CustId, LO.OrderDate
FROM LastOrder AS LO
WHERE LO.RowNum = 1

results:

结果:

+--------+------------+
| CustId | OrderDate  |
+--------+------------+
|      1 | 2019-09-01 |
|      2 | 2019-07-01 |
|      3 | 2017-01-01 |
|      4 | 2017-01-01 |
+--------+------------+

#2

You can use the MAX function to check if the latest date is in the future. If so, get the MIN date after today using MIN. Else get the latest date.

您可以使用MAX函数来检查最近的日期是否在将来。如果是的话，用MIN获取今天之后的最小日期，否则获取最近的日期。

SELECT CUSTID,OrderDate 
FROM (SELECT  CustId, 
              OrderDate, 
      CASE WHEN MAX(orderdate) OVER(PARTITION BY CustId) > GETUTCDATE() 
      THEN MIN(case when orderdate >getutcdate() then orderdate end) OVER(PARTITION BY CustId)
      ELSE MAX(orderdate) OVER(PARTITION BY CustId) end as latest_date
      FROM #CustOrder) T 
WHERE latest_date=orderDate

#3

Start your ORDER BY with a CASE expression that prefers future over past, and then use the ABS DATEDIFF (like you have now) as the second condition in the ORDER BY.

首先使用更喜欢future而不是past的CASE表达式开始您的订单，然后使用ABS DATEDIFF(就像您现在拥有的)作为ORDER BY中的第二个条件。

#4

Maybe create another column and use the LAG() window function to grab the last date function and then put a conditional/case statement within the select portion? https://msdn.microsoft.com/en-us/library/hh231256.aspx

也许创建另一个列并使用LAG()窗口函数获取最后一个日期函数，然后在select部分中放入条件/case语句?https://msdn.microsoft.com/en-us/library/hh231256.aspx

#5

Min, Max, UNION approach

最小、最大、工会的方法

select custID, MIN(OrderDate)
  from #CustOrder 
  where OrderDate > '2017-02-17'
 group by custID 
union all 
select co1.custID, max(co1.OrderDate)
  from #CustOrder co1
 where not exists ( select 1 
                      from #CustOrder co2 
                     where co2.CustId = co1.CustId 
                       and co2.OrderDate > '2017-02-17' 
                  )
 group by co1.custID

#1