按SQL语句对数据进行排序

Question

下表存储了personal_id和date的值：

  Person_ID    Effective_Date     End Effective_Date
  1)6335         24/02/1999
  2)6335         09/07/1998
  3)6335         26/06/1998

输出表应该如下所示

  Person_ID    Effective_Date     End Effective_Date
  1)6335         24/02/1999           31/12/9999
  2)6335         09/07/1998           23/02/1999
  3)6335         26/06/1998           08/07/1998

如果我使用java代码更新它，逻辑将会非常简单。但是，使用SQL语句就可以做到吗？我需要有人给我提供这样做的逻辑。我当前的终止生效日期将始终是下一个生效日期的前一天。假设我的第2行的生效日期是1988年9月7日，那么我的第1行的终止生效日期应该是它的前一天(1988年8月7日)。而我的最大生效日期的终止生效日期始终是31/12/9999。

Answer 1

希望这能帮到你。运行查询并检查结果。

DECLARE @tbl table (ID int, D1 DATETIME, D2 DATETIME)

INSERT INTO @tbl
select 1,'2/28/2013','2/28/2013'
union all
select 2,'3/2/2013','3/2/2013'
union all
select 3,'4/2/2013','4/2/2013'
union all
select 4,'4/6/2013','4/6/2013'
union all
select 5,'5/21/2013','5/21/2013'
union all
select 6,'6/10/2013','6/10/2013'

SELECT * FROM @tbl

UPDATE t1
SET t1.D2= DATEADD(DAY, -1, t2.D2)
FROM @tbl t1
     CROSS JOIN @tbl t2
WHERE t2.D1=(SELECT min(D1)
               FROM @tbl t
               WHERE D1>t1.D1)


SELECT * FROM @tbl

UPDATE @tbl
SET D2 = '12/31/9999'
WHERE D2 = (SELECT TOP 1 D2 FROM @tbl ORDER BY D2 DESC)

SELECT * FROM @tbl

这可能不是最有效的情况，但它假设您最初在D1和D2中使用相同的值。

Answer 2

您可以使用lead函数提前查看下一行并获取其生效日期：

select person_id, effective_date,
  lead(effective_date)
    over (partition by person_id order by effective_date) as lead_date
from t42;

 PERSON_ID EFFECTIVE_DATE LEAD_DATE
---------- -------------- ---------
      6335 26-JUN-98      09-JUL-98 
      6335 09-JUL-98      24-FEB-99 
      6335 24-FEB-99

然后，您可以使用它来执行更新。merge命令使这一点变得非常简单：

merge into t42
using (
  select person_id, effective_date,
    lead(effective_date)
      over (partition by person_id order by effective_date) as lead_date
  from t42
) t
on (t42.person_id = t.person_id and t42.effective_date = t.effective_date)
when matched then
update set t42.end_effective_date =
  case
    when t.lead_date is null then date '9999-12-31'
    else t.lead_date - 1
  end;

3 rows merged.

select * from t42;

 PERSON_ID EFFECTIVE_DATE END_EFFECTIVE_DATE
---------- -------------- ------------------
      6335 26-JUN-98      08-JUL-98          
      6335 09-JUL-98      23-FEB-99          
      6335 24-FEB-99      31-DEC-99          

using子句包含上面的代码片段，该代码片段从前一行获取日期。on子句将此日期与原始表进行匹配，并且对于匹配的行将终止有效日期更新为销售线索有效日期的前一天，或者如果没有提前期值(对于最近的行，则为‘当前’行)，则使用1999年的固定日期。

您的问题提到了更新，但如果您只想将结束日期作为结果集中的计算列，则简单得多：

select person_id, effective_date,
  case when lead_date is null then date '9999-12-31'
    else lead_date - 1 end as end_effective_date
from (
  select person_id, effective_date,
    lead(effective_date)
      over (partition by person_id order by effective_date) as lead_date
  from t42
);

 PERSON_ID EFFECTIVE_DATE END_EFFECTIVE_DATE
---------- -------------- ------------------
      6335 26-JUN-98      08-JUL-98          
      6335 09-JUL-98      23-FEB-99          
      6335 24-FEB-99      31-DEC-99          

Answer 3

 UPDATE personal_id SET [End Effective_Date] = To_date('31/12/9999', 'dd/mm/yyyy') 
 WHERE Person_ID = 6335 AND Effective_Date = To_date('24/02/1999', 'dd/mm/yyyy')