sql - 比较同表sql中的下一条记录

Question

我有一个包含 2 列 trans_date 和 amount 的表格。如果一条记录和下一条记录的转换差异是 1 天或同一天，我想要一个查询给我金额。

解释:

AMOUNT    TRANS_DATE

2645    2011-05-11 20:57:27.000
2640    2011-05-12 00:00:00.000
2645    2011-05-15 18:01:11.000
2645    2011-06-15 18:27:45.000
2645    2011-06-16 17:06:33.000
2645    2011-06-18 15:19:19.000
2645    2011-06-23 15:42:18.000

查询应该只显示我

 AMOUNT    TRANS_DATE

    2645    2011-05-11 20:57:27.000
    2640    2011-05-12 00:00:00.000
    2645    2011-05-15 18:01:11.000
    2645    2011-06-15 18:27:45.000
    2645    2011-06-16 17:06:33.000

我已经试过了

select DATEDIFF(DAY,a.TRANS_DATE,b.TRANS_DATE) from FIN_AP_PAYMENTS a inner join ( select * from (select a.*,rank() over (order by id) as ra from FIN_AP_PAYMENTS a, FIN_AP_PAYMENTS b )tbl )



select a.TRANS_DATE,b.TRANS_DATE,rank() over (order by a.id) as ra1,rank() over (order by b.id) as ra2 from FIN_AP_PAYMENTS a,FIN_AP_PAYMENTS b



select DATEDIFF(day,tbl.TRANS_DATE,tbl2.TRANS_DATE) from (select a.*,rank() over (order by id) as ra from FIN_AP_PAYMENTS a) tbl inner join (select a.*,rank() over (order by a.id) as ra1 from FIN_AP_PAYMENTS a ) tbl2 on tbl.id=tbl2.id

Answer 1

使用 lead() 和 lag() 获取下一个和上一个值。然后检查它们之间的时间进行过滤:

select t.amount, t.trans_date
from (select t.*, lead(trans_date) over (order by trans_date) as next_td,
             lag(trans_date) over (order by trans_date) as prev_td
      from FIN_AP_PAYMENTS t
     ) t
where datediff(second, prev_td, trans_date) < 24*60*60 or
      datediff(second, trans_date, next_trans_date) < 24*60*60;

编辑:

在 SQL Server 2008 中，您可以使用 outer apply 执行此操作:

select t.amount, t.trans_date
from (select t.*, tlead.trans_date as next_td,
             tlag.trans_date as prev_td
      from FIN_AP_PAYMENTS t outer apply
           (select top 1 t2.*
            from FIN_AP_PAYMENTS t2
            where t2.trans_date < t.trans_date
            order by trans_date desc
           ) tlag outer apply
           (select top 1 t2.*
            from FIN_AP_PAYMENTS t2
            where t2.trans_date > t.trans_date
            order by trans_date asc
           ) tlead         
     ) t
where datediff(second, prev_td, trans_date) < 24*60*60 or
      datediff(second, trans_date, next_trans_date) < 24*60*60;