USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[liu_shui](
[d_date] [
date
] NOT NULL,
[i_id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_liu_shui] PRIMARY KEY CLUSTERED
(
[i_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[liu_shui] ON
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA73A0B00 AS
Date
), 1)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA83A0B00 AS
Date
), 2)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA93A0B00 AS
Date
), 3)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAA3A0B00 AS
Date
), 4)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAB3A0B00 AS
Date
), 5)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAD3A0B00 AS
Date
), 6)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAF3A0B00 AS
Date
), 8)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB03A0B00 AS
Date
), 9)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB23A0B00 AS
Date
), 11)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB33A0B00 AS
Date
), 12)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB53A0B00 AS
Date
), 13)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB83A0B00 AS
Date
), 14)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB93A0B00 AS
Date
), 15)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBA3A0B00 AS
Date
), 16)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBB3A0B00 AS
Date
), 17)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBC3A0B00 AS
Date
), 18)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBD3A0B00 AS
Date
), 19)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBE3A0B00 AS
Date
), 20)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBF3A0B00 AS
Date
), 21)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC03A0B00 AS
Date
), 22)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC13A0B00 AS
Date
), 23)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC23A0B00 AS
Date
), 24)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC33A0B00 AS
Date
), 25)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC43A0B00 AS
Date
), 26)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC53A0B00 AS
Date
), 27)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC63A0B00 AS
Date
), 28)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC73A0B00 AS
Date
), 29)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC83A0B00 AS
Date
), 30)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC93A0B00 AS
Date
), 31)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCB3A0B00 AS
Date
), 32)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCC3A0B00 AS
Date
), 33)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCD3A0B00 AS
Date
), 34)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCE3A0B00 AS
Date
), 35)
INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCF3A0B00 AS
Date
), 36)
INSERT [dbo].[liu_shui] ([d_date], [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
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
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