?
计算下条记录与上条记录日期天数之差(二)
[i_id]) VALUES (CAST(0xD03A0B00 AS Date), 37)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD13A0B00 AS Date), 38)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD33A0B00 AS Date), 39)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD63A0B00 AS Date), 40)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD73A0B00 AS Date), 41)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD83A0B00 AS Date), 42)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD93A0B00 AS Date), 43)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDA3A0B00 AS Date), 44)
SET IDENTITY_INSERT [dbo].[liu_shui] OFF
go
/******计算日期差，方法1 ******/
SELECT
a.[i_id]
,a.[d_date]
,日期差=
COALESCE(DATEDIFF(DAY,b.d_date,a.d_date),0)
FROM [test].[dbo].[liu_shui] as a
outer apply
(select b.d_date from test.dbo.liu_shui b
where a.i_id-b.i_id=1) as b
go
/*计算日期差，方法2*/
SELECT
a.[i_id]
,a.[d_date]
,日期差=
coalesce(
(
select DATEDIFF(DAY,b.d_date,a.d_date) from test.dbo.liu_shui b
where a.i_id-b.i_id =1
),0
)
FROM [test].[dbo].[liu_shui] as a