两段录音之间的天数差异

Question

我有这个表

User | days
A    | 1
A    | 1   
A    | 2
B    | 2
B    | 5

和

User | difference_in_day
A    | 0
A    | 1   
B    | 3

我想我必须做一个自连接，但我不知道如何比较一行下一个。

我们需要在第一个表中添加行位置吗?:

WHERE  a.row_position+1=b.row_position 

Answer 1

请尝试:

;with T as(
    select *, ROW_NUMBER() over (order by User, Days) Rnum from YourTable
)
select 
    distinct a.User, 
    b.Days-a.Days difference_in_day 
from T a left join T b on a.Rnum=b.Rnum-1 
where b.User is not null

示例

declare @tbl as table(xUser nvarchar(1), xDays int)
insert into @tbl values 
('A', 1),
('A', 1),
('A', 2),
('B', 2),
('B', 5)
select *, ROW_NUMBER() over (order by xUser, xDays) Rnum from @tbl
;with T as(
    select *, ROW_NUMBER() over (order by xUser, xDays) Rnum from @tbl
)
select 
    distinct a.xUser, 
    b.xDays-a.xDays difference_in_day 
from T a left join T b on a.Rnum=b.Rnum-1 
where b.xUser is not null