计算下条记录与上条记录日期天数之差_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了计算下条记录与上条记录日期天数之差_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4396字，纯文字阅读大概需要7分钟。

内容图文

USE [test]
GO
/****** Object:  Table [dbo].[liu_shui]    Script Date: 11/13/2015 09:34:50 ******/
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



/******计算日期差，方法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

内容总结

以上是互联网集市为您收集整理的计算下条记录与上条记录日期天数之差_MySQL全部内容，希望文章能够帮你解决计算下条记录与上条记录日期天数之差_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端