Maison > base de données > tutoriel mysql > 计算下条记录与上条记录日期天数之差_MySQL

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Libérer: 2016-05-30 17:10:00
original
1415 Les gens l'ont consulté

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

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

Copier après la connexion

 

Étiquettes associées:
Déclaration de ce site Web
Le contenu de cet article est volontairement contribué par les internautes et les droits d'auteur appartiennent à l'auteur original. Ce site n'assume aucune responsabilité légale correspondante. Si vous trouvez un contenu suspecté de plagiat ou de contrefaçon, veuillez contacter admin@php.cn
Tutoriels populaires
Plus>
Derniers téléchargements
Plus>
effets Web
Code source du site Web
Matériel du site Web
Modèle frontal