SQL Server 按最接近零的值排序

Question

我在表中有一些重复的值，我正在尝试使用 Row_Number 将它们过滤掉。我想使用 datediff 对行进行排序，并根据最接近零的值对结果进行排序，但我很难解释负值。

以下是数据示例和我当前的 Row_Number 字段 (rn) 列:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       10/02/2015  30/03/2015      -48             1
93638       27/03/2015  30/03/2015      -3              2
250575      23/10/2014  29/10/2014      -6              1
250575      19/11/2014  24/11/2014      -5              2
203312      23/01/2015  26/01/2015      -3              1
203312      26/01/2015  26/01/2015      0               2
387737      19/02/2015  26/02/2015      -7              1
387737      26/02/2015  26/02/2015      0               2
751915      02/04/2015  04/04/2015      -2              1
751915      10/04/2015  25/03/2015      16              2
712364      24/01/2015  30/01/2015      -6              1
712364      26/01/2015  30/01/2015      -4              2

我对上面的选择语句是:

select 
    PersonID, SurveyDate, DischargeDate, 
    datediff(dd,dischargedate,surveydate) days, 
    ROW_NUMBER () over (partition by PersonID 
                        order by datediff(dd, dischargedate, surveydate) asc) as rn
from 
    Table 1
order by 
    PersonID, rn

我想做的是更改排序顺序，使其显示如下:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       27/03/2015  30/03/2015      -3              1
93638       10/02/2015  30/03/2015      -48             2
250575      19/11/2014  24/11/2014      -5              1
250575      23/10/2014  29/10/2014      -6              2

因此，最接近 DischargeDate 的 DaysToSurvey 值被列为 rn 1。

这可能吗？

Answer 1

你已经接近了。只需添加 ABS()计算差异的绝对值:

ROW_NUMBER () OVER (
  PARTITION BY PersonID 
  ORDER BY abs(datediff(dd, dischargedate, surveydate)) asc
) AS rn