SQL计算一张表中两个日期之间的天数 [英] SQL Calculate Days between two dates in one table

问题描述

我有一个表 dbo.Trans，其中包含一个名为 bd_id(varchar) 和 transfer_date(Datetime) 的 ID，也是一个标识符 member_id pk 是 trns_id 并且是连续的

I have a table dbo.Trans which contains an id called bd_id(varchar) and transfer_date(Datetime), also an identifier member_id pk is trns_id and is sequential

表中存在 bd_id 和 member_id 的重复项.

Duplicates of bd_id and member_id exist in the table.

transfer_date       |bd_id| member_id | trns_id
2008-01-01 00:00:00 | 432 | 111       | 1  
2008-01-03 00:00:00 | 123 | 111       | 2  
2008-01-08 00:00:00 | 128 | 111       | 3  
2008-02-04 00:00:00 | 123 | 432       | 4
.......

对于每个member_id，我想获取日期之间的天数以及每个bd_id

For each member_id, I want to get the amount of days between dates and for each bd_id

EG，成员 111 从 2008-01-01 到 2008-02-01 使用 432 所以返回应该是 2
然后接下来是 5

E.G., member 111 used 432 from 2008-01-01 until 2008-02-01 so return should be 2
Then next would be 5

我知道 DATEDIFF() 函数存在，但是当日期在同一个表中时我不确定如何获得差异.

I know the DATEDIFF() function exists but I am not sure how to get the difference when dates are in the same table.

感谢任何帮助.

推荐答案

你可以尝试这样的事情.

You could try something like this.

select T1.member_id,
       datediff(day, T1.transfer_date, T3.transfer_date) as DD
from YourTable as T1
  cross apply (select top 1 T2.transfer_date
               from YourTable as T2
               where T2.transfer_date > T1.transfer_date and
                     T2.member_id = T1.member_id
               order by T2.transfer_date) as T3

SE-Data

这篇关于SQL计算一张表中两个日期之间的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！