一个计算日期连续性的脚本

最新推荐文章于 2021-02-08 08:00:41 发布
转载 最新推荐文章于 2021-02-08 08:00:41 发布 · 179 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/bgbird/p/4956029.html 

  1 USE [test]
  2 GO
  3 
  4 --一个计算日期连续性的脚本
  5 --需求：查找指定天数的连续交易的记录，比如连续2天发生，连续3天发生等等。 
  6 /****** Object:  Table [dbo].[liu_shui]    Script Date: 11/11/2015 14:36:05 ******/
  7 SET ANSI_NULLS ON
  8 GO
  9 SET QUOTED_IDENTIFIER ON
 10 GO
 11 CREATE TABLE [dbo].[liu_shui](
 12     [d_date] [date] NOT NULL,
 13     [i_id] [int] IDENTITY(1,1) NOT NULL,
 14  CONSTRAINT [PK_liu_shui] PRIMARY KEY CLUSTERED 
 15 (
 16     [i_id] ASC
 17 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 18 ) ON [PRIMARY]
 19 GO
 20 SET IDENTITY_INSERT [dbo].[liu_shui] ON
 21 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA73A0B00 AS Date), 1)
 22 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA83A0B00 AS Date), 2)
 23 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA93A0B00 AS Date), 3)
 24 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAA3A0B00 AS Date), 4)
 25 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAB3A0B00 AS Date), 5)
 26 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAD3A0B00 AS Date), 6)
 27 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAF3A0B00 AS Date), 8)
 28 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB03A0B00 AS Date), 9)
 29 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB23A0B00 AS Date), 11)
 30 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB33A0B00 AS Date), 12)
 31 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB53A0B00 AS Date), 13)
 32 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB83A0B00 AS Date), 14)
 33 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB93A0B00 AS Date), 15)
 34 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBA3A0B00 AS Date), 16)
 35 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBB3A0B00 AS Date), 17)
 36 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBC3A0B00 AS Date), 18)
 37 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBD3A0B00 AS Date), 19)
 38 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBE3A0B00 AS Date), 20)
 39 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBF3A0B00 AS Date), 21)
 40 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC03A0B00 AS Date), 22)
 41 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC13A0B00 AS Date), 23)
 42 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC23A0B00 AS Date), 24)
 43 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC33A0B00 AS Date), 25)
 44 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC43A0B00 AS Date), 26)
 45 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC53A0B00 AS Date), 27)
 46 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC63A0B00 AS Date), 28)
 47 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC73A0B00 AS Date), 29)
 48 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC83A0B00 AS Date), 30)
 49 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC93A0B00 AS Date), 31)
 50 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCB3A0B00 AS Date), 32)
 51 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCC3A0B00 AS Date), 33)
 52 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCD3A0B00 AS Date), 34)
 53 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCE3A0B00 AS Date), 35)
 54 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCF3A0B00 AS Date), 36)
 55 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD03A0B00 AS Date), 37)
 56 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD13A0B00 AS Date), 38)
 57 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD33A0B00 AS Date), 39)
 58 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD63A0B00 AS Date), 40)
 59 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD73A0B00 AS Date), 41)
 60 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD83A0B00 AS Date), 42)
 61 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD93A0B00 AS Date), 43)
 62 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDA3A0B00 AS Date), 44)
 63 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDB3A0B00 AS Date), 45)
 64 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDC3A0B00 AS Date), 46)
 65 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDD3A0B00 AS Date), 47)
 66 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDE3A0B00 AS Date), 48)
 67 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDF3A0B00 AS Date), 49)
 68 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE03A0B00 AS Date), 50)
 69 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE13A0B00 AS Date), 51)
 70 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE23A0B00 AS Date), 52)
 71 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE33A0B00 AS Date), 53)
 72 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE43A0B00 AS Date), 54)
 73 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE53A0B00 AS Date), 55)
 74 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE63A0B00 AS Date), 56)
 75 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE73A0B00 AS Date), 57)
 76 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE83A0B00 AS Date), 58)
 77 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEA3A0B00 AS Date), 59)
 78 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEB3A0B00 AS Date), 60)
 79 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEC3A0B00 AS Date), 61)
 80 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xED3A0B00 AS Date), 62)
 81 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEE3A0B00 AS Date), 63)
 82 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEF3A0B00 AS Date), 64)
 83 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF03A0B00 AS Date), 65)
 84 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF23A0B00 AS Date), 66)
 85 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF53A0B00 AS Date), 67)
 86 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF63A0B00 AS Date), 68)
 87 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF73A0B00 AS Date), 69)
 88 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF83A0B00 AS Date), 70)
 89 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF93A0B00 AS Date), 71)
 90 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFA3A0B00 AS Date), 72)
 91 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFB3A0B00 AS Date), 73)
 92 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFC3A0B00 AS Date), 74)
 93 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFD3A0B00 AS Date), 75)
 94 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFE3A0B00 AS Date), 76)
 95 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFF3A0B00 AS Date), 77)
 96 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x003B0B00 AS Date), 78)
 97 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x013B0B00 AS Date), 79)
 98 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x023B0B00 AS Date), 80)
 99 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x033B0B00 AS Date), 81)
100 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x043B0B00 AS Date), 82)
101 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x053B0B00 AS Date), 83)
102 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x063B0B00 AS Date), 84)
103 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x073B0B00 AS Date), 85)
104 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x093B0B00 AS Date), 86)
105 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0A3B0B00 AS Date), 87)
106 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0B3B0B00 AS Date), 88)
107 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0C3B0B00 AS Date), 89)
108 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0D3B0B00 AS Date), 90)
109 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0E3B0B00 AS Date), 91)
110 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0F3B0B00 AS Date), 92)
111 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x113B0B00 AS Date), 93)
112 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x143B0B00 AS Date), 94)
113 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x153B0B00 AS Date), 95)
114 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x163B0B00 AS Date), 96)
115 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x173B0B00 AS Date), 97)
116 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x183B0B00 AS Date), 98)
117 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x193B0B00 AS Date), 99)
118 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1A3B0B00 AS Date), 100)
119 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1B3B0B00 AS Date), 101)
120 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1C3B0B00 AS Date), 102)
121 GO
122 print 'Processed 100 total records'
123 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1D3B0B00 AS Date), 103)
124 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1E3B0B00 AS Date), 104)
125 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x1F3B0B00 AS Date), 105)
126 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x203B0B00 AS Date), 106)
127 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x213B0B00 AS Date), 107)
128 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x223B0B00 AS Date), 108)
129 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x233B0B00 AS Date), 109)
130 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x243B0B00 AS Date), 110)
131 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x263B0B00 AS Date), 111)
132 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x273B0B00 AS Date), 112)
133 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x283B0B00 AS Date), 113)
134 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x293B0B00 AS Date), 114)
135 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x2A3B0B00 AS Date), 115)
136 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x2B3B0B00 AS Date), 116)
137 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x2C3B0B00 AS Date), 117)
138 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x2E3B0B00 AS Date), 118)
139 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x313B0B00 AS Date), 119)
140 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x323B0B00 AS Date), 120)
141 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x333B0B00 AS Date), 121)
142 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x343B0B00 AS Date), 122)
143 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x353B0B00 AS Date), 123)
144 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x363B0B00 AS Date), 124)
145 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x373B0B00 AS Date), 125)
146 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x383B0B00 AS Date), 126)
147 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x393B0B00 AS Date), 127)
148 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3A3B0B00 AS Date), 128)
149 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3B3B0B00 AS Date), 129)
150 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3C3B0B00 AS Date), 130)
151 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3D3B0B00 AS Date), 131)
152 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3E3B0B00 AS Date), 132)
153 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x3F3B0B00 AS Date), 133)
154 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x403B0B00 AS Date), 134)
155 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x413B0B00 AS Date), 135)
156 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x423B0B00 AS Date), 136)
157 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x433B0B00 AS Date), 137)
158 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x453B0B00 AS Date), 138)
159 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x463B0B00 AS Date), 139)
160 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x473B0B00 AS Date), 140)
161 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x483B0B00 AS Date), 141)
162 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x493B0B00 AS Date), 142)
163 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x4A3B0B00 AS Date), 143)
164 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x4B3B0B00 AS Date), 144)
165 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x4D3B0B00 AS Date), 145)
166 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x503B0B00 AS Date), 146)
167 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x513B0B00 AS Date), 147)
168 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x523B0B00 AS Date), 148)
169 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x533B0B00 AS Date), 149)
170 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x543B0B00 AS Date), 150)
171 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x553B0B00 AS Date), 151)
172 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x563B0B00 AS Date), 152)
173 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x573B0B00 AS Date), 153)
174 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x583B0B00 AS Date), 154)
175 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x593B0B00 AS Date), 155)
176 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5A3B0B00 AS Date), 156)
177 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5B3B0B00 AS Date), 157)
178 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5C3B0B00 AS Date), 158)
179 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5D3B0B00 AS Date), 159)
180 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5E3B0B00 AS Date), 160)
181 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x5F3B0B00 AS Date), 161)
182 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x603B0B00 AS Date), 162)
183 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x613B0B00 AS Date), 163)
184 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x633B0B00 AS Date), 164)
185 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x643B0B00 AS Date), 165)
186 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x653B0B00 AS Date), 166)
187 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x663B0B00 AS Date), 167)
188 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x673B0B00 AS Date), 168)
189 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x683B0B00 AS Date), 169)
190 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x693B0B00 AS Date), 170)
191 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x6B3B0B00 AS Date), 171)
192 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x6E3B0B00 AS Date), 172)
193 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x6F3B0B00 AS Date), 173)
194 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x703B0B00 AS Date), 174)
195 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x713B0B00 AS Date), 175)
196 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x723B0B00 AS Date), 176)
197 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x733B0B00 AS Date), 177)
198 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x743B0B00 AS Date), 178)
199 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x753B0B00 AS Date), 179)
200 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x763B0B00 AS Date), 180)
201 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x773B0B00 AS Date), 181)
202 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x783B0B00 AS Date), 182)
203 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x793B0B00 AS Date), 183)
204 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7A3B0B00 AS Date), 184)
205 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7B3B0B00 AS Date), 185)
206 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7C3B0B00 AS Date), 186)
207 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7D3B0B00 AS Date), 187)
208 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7E3B0B00 AS Date), 188)
209 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x7F3B0B00 AS Date), 189)
210 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x803B0B00 AS Date), 190)
211 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x823B0B00 AS Date), 191)
212 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x833B0B00 AS Date), 192)
213 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x843B0B00 AS Date), 193)
214 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x853B0B00 AS Date), 194)
215 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x863B0B00 AS Date), 195)
216 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x873B0B00 AS Date), 196)
217 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x883B0B00 AS Date), 197)
218 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x8A3B0B00 AS Date), 198)
219 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x8D3B0B00 AS Date), 199)
220 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x8E3B0B00 AS Date), 200)
221 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x8F3B0B00 AS Date), 201)
222 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x903B0B00 AS Date), 202)
223 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x913B0B00 AS Date), 203)
224 GO
225 print 'Processed 200 total records'
226 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x923B0B00 AS Date), 204)
227 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x933B0B00 AS Date), 205)
228 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x943B0B00 AS Date), 206)
229 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x953B0B00 AS Date), 207)
230 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x963B0B00 AS Date), 208)
231 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x973B0B00 AS Date), 209)
232 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x983B0B00 AS Date), 210)
233 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x993B0B00 AS Date), 211)
234 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x9A3B0B00 AS Date), 212)
235 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x9B3B0B00 AS Date), 213)
236 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x9C3B0B00 AS Date), 214)
237 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x9D3B0B00 AS Date), 215)
238 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x9E3B0B00 AS Date), 216)
239 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA03B0B00 AS Date), 217)
240 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA13B0B00 AS Date), 218)
241 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA23B0B00 AS Date), 219)
242 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA33B0B00 AS Date), 220)
243 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA43B0B00 AS Date), 221)
244 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA53B0B00 AS Date), 222)
245 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA63B0B00 AS Date), 223)
246 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xA83B0B00 AS Date), 224)
247 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAB3B0B00 AS Date), 225)
248 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAC3B0B00 AS Date), 226)
249 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAD3B0B00 AS Date), 227)
250 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAE3B0B00 AS Date), 228)
251 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xAF3B0B00 AS Date), 229)
252 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB03B0B00 AS Date), 230)
253 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB13B0B00 AS Date), 231)
254 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB23B0B00 AS Date), 232)
255 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB33B0B00 AS Date), 233)
256 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB43B0B00 AS Date), 234)
257 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB53B0B00 AS Date), 235)
258 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB63B0B00 AS Date), 236)
259 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB73B0B00 AS Date), 237)
260 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB83B0B00 AS Date), 238)
261 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xB93B0B00 AS Date), 239)
262 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBA3B0B00 AS Date), 240)
263 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBB3B0B00 AS Date), 241)
264 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBC3B0B00 AS Date), 242)
265 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBD3B0B00 AS Date), 243)
266 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xBF3B0B00 AS Date), 244)
267 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC03B0B00 AS Date), 245)
268 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC13B0B00 AS Date), 246)
269 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC23B0B00 AS Date), 247)
270 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC33B0B00 AS Date), 248)
271 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC43B0B00 AS Date), 249)
272 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC53B0B00 AS Date), 250)
273 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xC73B0B00 AS Date), 251)
274 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCA3B0B00 AS Date), 252)
275 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCB3B0B00 AS Date), 253)
276 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCC3B0B00 AS Date), 254)
277 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCD3B0B00 AS Date), 255)
278 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCE3B0B00 AS Date), 256)
279 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xCF3B0B00 AS Date), 257)
280 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD03B0B00 AS Date), 258)
281 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD13B0B00 AS Date), 259)
282 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD23B0B00 AS Date), 260)
283 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD33B0B00 AS Date), 261)
284 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD43B0B00 AS Date), 262)
285 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD53B0B00 AS Date), 263)
286 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD63B0B00 AS Date), 264)
287 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD73B0B00 AS Date), 265)
288 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD83B0B00 AS Date), 266)
289 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xD93B0B00 AS Date), 267)
290 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDA3B0B00 AS Date), 268)
291 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDB3B0B00 AS Date), 269)
292 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDC3B0B00 AS Date), 270)
293 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDE3B0B00 AS Date), 271)
294 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xDF3B0B00 AS Date), 272)
295 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE03B0B00 AS Date), 273)
296 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE13B0B00 AS Date), 274)
297 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE23B0B00 AS Date), 275)
298 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE33B0B00 AS Date), 276)
299 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE43B0B00 AS Date), 277)
300 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE63B0B00 AS Date), 278)
301 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xE93B0B00 AS Date), 279)
302 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEA3B0B00 AS Date), 280)
303 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEB3B0B00 AS Date), 281)
304 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEC3B0B00 AS Date), 282)
305 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xED3B0B00 AS Date), 283)
306 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEE3B0B00 AS Date), 284)
307 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xEF3B0B00 AS Date), 285)
308 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF03B0B00 AS Date), 286)
309 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF13B0B00 AS Date), 287)
310 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF23B0B00 AS Date), 288)
311 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF33B0B00 AS Date), 289)
312 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF43B0B00 AS Date), 290)
313 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF53B0B00 AS Date), 291)
314 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF63B0B00 AS Date), 292)
315 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF73B0B00 AS Date), 293)
316 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF83B0B00 AS Date), 294)
317 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xF93B0B00 AS Date), 295)
318 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFA3B0B00 AS Date), 296)
319 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFC3B0B00 AS Date), 297)
320 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFD3B0B00 AS Date), 298)
321 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFE3B0B00 AS Date), 299)
322 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0xFF3B0B00 AS Date), 300)
323 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x003C0B00 AS Date), 301)
324 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x013C0B00 AS Date), 302)
325 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x023C0B00 AS Date), 303)
326 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x043C0B00 AS Date), 304)
327 GO
328 print 'Processed 300 total records'
329 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x073C0B00 AS Date), 305)
330 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x083C0B00 AS Date), 306)
331 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x093C0B00 AS Date), 307)
332 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0A3C0B00 AS Date), 308)
333 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0B3C0B00 AS Date), 309)
334 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0C3C0B00 AS Date), 310)
335 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0D3C0B00 AS Date), 311)
336 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0E3C0B00 AS Date), 312)
337 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x0F3C0B00 AS Date), 313)
338 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x103C0B00 AS Date), 314)
339 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x113C0B00 AS Date), 315)
340 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x123C0B00 AS Date), 316)
341 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x133C0B00 AS Date), 317)
342 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x143C0B00 AS Date), 318)
343 INSERT [dbo].[liu_shui] ([d_date], [i_id]) VALUES (CAST(0x153C0B00 AS Date), 319)
344 SET IDENTITY_INSERT [dbo].[liu_shui] OFF
345 
346 
347 --输入任何一个日期，查找其之前和之后的连续的日期序列
348 create function Func_Continuity(@d_start_date date)--+1时点之后的日期序列，-1时点之前的日期序列
349 returns table
350 as
351 return
352 (
353     with cte_end 
354     as
355         (
356          select i_id,d_date
357          from dbo.liu_shui 
358          where d_date=@d_start_date 
359          union all
360          select a.i_id,a.d_date
361          from dbo.liu_shui a
362          inner join cte_end as b
363          on DATEDIFF(day,b.d_date,a.d_date)=1--时点之后的日期序列
364         )
365     ,cte_begin
366     as
367     (
368          select i_id,d_date
369          from dbo.liu_shui 
370          where d_date=@d_start_date 
371          union all
372          select a.i_id,a.d_date
373          from dbo.liu_shui a
374          inner join cte_begin as b
375          on DATEDIFF(day,b.d_date,a.d_date)=-1--时点之前的日期序列
376         )
377     select * from cte_begin 
378     union 
379     select * from cte_end 
380 )
381 go
382 
383 
384 select i_id,d_date  from 
385     (
386         select a.i_id,a.d_date,b.d_date as b_d_date from dbo.liu_shui  as a
387          cross  apply  Func_Continuity(a.d_date) as b
388     ) as tt
389 group by i_id,d_date 
390 having COUNT(b_d_date)>=3--大于等于几就是连续多少天及以上发生
391 order by d_date

 

转载于:https://www.cnblogs.com/bgbird/p/4956029.html

cmd-bat-批处理-脚本-计算100以内数的和.zip
05-20
一个基本的批处理脚本通常包含一个文件头，说明脚本的作者、创建日期和脚本用途，然后是脚本的主要逻辑部分，包括变量声明、命令执行以及错误处理等。脚本的末尾可以包含注释信息，用于对脚本功能进行解释说明。 ...
Kozachenko-Leonenko 方法的熵估计：脚本通过 Kozachenko-Leonenko 方法计算一维日期的熵点估计。-matlab开发
05-29
在IT领域，尤其是在数据分析和信号处理中，熵是一个重要的概念，它用来衡量数据的不确定性或信息的丰富程度。本文将详细介绍Kozachenko-Leonenko方法及其在MATLAB中的实现，以此来估计一维数据的熵。 Kozachenko-...
ORCALE : 计算满足某种条件的连续日期的天数
ZeWe的博客
03-20 1424
最近有一个需求：查询满足某种条件的数据，且需求满足该条件连续的天数。 模拟一个场景：如下面这张表 求昨日，日销量大于5000的商店商品，且计算已经连续多少日。结果如下： 想法： 根据 门店编号，商品编号分组，日期倒序排序，求排名数，并且计算该条数据日期到今日的差值。 如果该差值等于排名数，这证明天数连续，count（*）求满足条件的数值。 SQL语句： with...
SQL Server 2008 R2导出数据脚本和导入数据库脚本的方法（原创+转载）
以梦为马 不负韶华
10-08 7802
以前看到有些朋友说必须SQL Server 2008才能导出包含数据的脚本，后来仔细研究发现其实SQL Server 2008 R2也是可以的，只需在导出的时候在高级中设置一下即可。 1.首先在数据库上右键依次选择任务=>生成脚本。 2.在弹出的窗口中选择要操作的表，然后点击下一步。 3.点击窗口上的高级按钮。 4.把滚动条拉到最下面会看到“要编写脚本的数据的类型”，在下...
mysql 日期连续性_一个计算日期连续性的脚本_MySQL
weixin_31108095的博客
01-19 233
1 USE [test]2 GO34 --一个计算日期连续性的脚本5 --需求：查找指定天数的连续交易的记录，比如连续2天发生，连续3天发生等等。6 /****** Object: Table [dbo].[liu_shui] Script Date: 11/11/2015 14:36:05 ******/7 SET ANSI_NULLS ON8 GO9 SET QUOTED_IDENTI...
Visual FoxPro 9.0 报表书写器的新增功能
热门推荐
张洪举
05-29 1万+
Visual FoxPro 9.0 报表书写器的新增功能作者：Cathy Pountney，Visual FoxPro MVP,www.frontier2000.com翻译：张洪举，Visual FoxPro MVP，www.vfptop.com 应用于：Visual FoxPro 9.0概述: 学习Visual FoxPro 9.0报表书写器的新增功能包括新增的可重用
各省市数据-SqlServer版SQL语句数据
M义薄云天的博客
05-15 818
SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[Province]( [ID] [int] NOT NULL, [Name] [varchar](20) NOT NULL, CONSTRAINT [PK_Province] PRIMARY KEY CLUSTERED ( [I...
jQuery插件datepicker 日期连续选择
10-24
本文主要讲解了如何使用jQuery的datepicker插件实现日期连续选择的功能，并通过代码实例展示实现方法。 首先，我们了解jQuery UI的datepicker组件的基本用法。它是jQuery UI库提供的一个可交互的日期选择组件，能够...
MATLAB脚本用于在真值日期和EME2000笛卡尔坐标系之间转换（NOVAS版本）.rar
02-10
MATLAB（Matrix Laboratory的缩写）是一种用于算法开发、数据可视化、数据分析以及数值计算的高性能编程语言和交互式环境。...该脚本的出现，为需要进行天体位置分析的科研人员和学生提供了一个强有力的计算工具。
SQL Server 连续n天问题
04-21
- 示例脚本可能包括一个内联CTE，用于生成一个包含日期范围内的所有日期的列表。然后通过左连接UserLogins表和CTE，将用户登录日期与生成的日期范围匹配。 - 通过对匹配结果进行分组（GROUP BY UserID, LoginDate...
atitit.sql server2008导出导入数据库大的表格文件... oracle mysql
weixin_30784945的博客
09-03 158
atitit.sqlserver2008导出导入数据库大的表格文件... 1.超过80M的文件是不能在查询分析器中执行的 1 2.Oracle,mysql大的文件导入 1 2.1.使用sql文件 1 2.2.使用dmp二进制文件(oracleonly) 1 2.3.Other导出txt,excel在导入(不推荐),常常不能导入 1 3.本机导入 ...
全国车牌归属地对应表 - sqlserver
春华秋实
10-30 3587
若需要全国各个地区车牌归属地对应关系表，在sql server数据库中执行以下语句： SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PlateNum]( [Id] [int] IDENTITY(1,1) NOT NULL, [AliasName] [nchar](1) NOT NULL, [PlateCod...
趣味题2：判断记录连续性的问题
ghostgant的专栏
06-30 1174
<br />1、大致的需求是使用SQL来判断记录的连续性，并找到每段记录的最小值和最大值。<br />    核心思想是通过和ROWNUM的比较，给不同的时间段分配不同的值，然后根据这个值进行GROUP。需要额外注意的是，首先要保证记录是按照顺序读取的，否则的话，会导致记录错误的分段。其次需要注意的是时间的处理，这里是将年和月的信息存在一个字符串中，但是判断记录是否连续需要根据时间的规则处理。而且，每个月包含的天数是不一致的，因此，需要使用处理月份的函数。<br />select min(time) ||‘
连续签到，只计算连续的天数，断掉则为0重新计算
yzx4321的博客
07-26 3864
1.获取到今天和昨天的时间,如果发现今天或昨天没有签到记录,则 连续签到为0 2.如果今天或昨天发现签到记录,根据签到日期倒序,然后生成最大(最近)的签到时间 - 1天的自然日,将两列日期进行对比,得到连续签到天数 SELECT count(1) FROM ( SELECT date_sub(a.create_date, INTERVAL 1 DAY) signDate, ( @i :...
使用SQL优雅的计算日期连续性问题
尼克不可的博客
10-24 2845
文章向导一、造数据二、查看数据结构三、需求（计算出数据中每个用户的最长连续访问年数） 一、造数据 DROP TABLE IF EXISTS 优快云_TEST; CREATE TABLE 优快云_TEST ( user_id CHAR(4) COMMENT '用户id', access_year INT(4) COMMENT '访问年' ); INSERT INTO 优快云_TEST VALUES ('0001',1999), ('0002',1999), ('0001',2000), ('00
全国省-市-县区，三级一表，SQL脚本
Lucky_For_Us的专栏
07-07 3261
/****** Object: Table [dbo].[TArea] Script Date: 07/07/2015 14:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TArea]( [AreaID] [int] IDENTITY(1,1) NOT NULL, [
SQL 问题1：连续日期的识别与连续天数的计算
weixin_47385264的博客
08-06 1266
应用场景： 分析业务问题中，满足条件：电流在正负2A之间且持续时间>2天的业务主体，若期间恢复正常，则重新开始计算 SQL梳理： 1.筛选条件： 1.1 电流在正负2A之间； 1.2 持续时间>2天 2.计算难点： 时间的连续性 3.已尝试路径： 3.1网上查询 可搜索到MySQL server的应用具体案例，但是涉及到循环，所以目前在实际SQL编写当中还没能具体写出来语句 3.2缓冲办法 在满足筛选条件的前提下，计算日期的count（）值 ...
mysql导出sql和表格文件大小_atitit.sql server2008导出导入数据库大的表格文件... oracle mysql...
weixin_42514002的博客
02-08 157
atitit.sqlserver2008导出导入数据库大的表格文件...1.超过80M的文件是不能在查询分析器中执行的imExtxt不好使用,常常不能im...最佳实践还是使用sql做交流...最近经常用到超过80M*.sql文件的导入问题。上网找了一下，发现超过80M的文件是不能在查询分析器中执行的。2.Oracle,mysql大的文件导入2.1.使用sql文件2.2.使用dm...
全国省市县数据库 SQLServer版
云上人间钦自赏
06-24 7777
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Area]( [codeID] [char](6) NOT NULL, [name] [nvarchar](50) NOT NULL, [parentID] [char](6) NOT NULL ) ON [PR
python脚本用不了换一个
最新发布
05-09
<think>好的，我需要帮助用户找到一个替代Python脚本的方法，在Windows系统中转换Kernel Log的时间格式。用户之前使用的是Python脚本，但现在可能因为某些原因无法使用，需要其他替代方案。我需要参考用户提供的引用内容，特别是引用[1]中提到在shell脚本中嵌入其他语言的方法，比如Perl、Ruby、AWK等。同时，引用[2]提到了Python的.pyc文件运行机制，但可能不直接相关。用户的问题是要替换当前的Python脚本，所以需要考虑其他脚本语言或工具。 首先，我需要确认用户的需求：转换Kernel Log的时间戳格式，原来的Python脚本可能处理的是类似[秒数.毫秒]的格式，转换成可读的日期时间。用户现在需要替代方案，可能包括其他脚本语言或命令行工具。 根据引用[1]，可以在shell脚本中嵌入Perl、Ruby、AWK等。考虑到Windows系统，虽然原生支持批处理脚本，但可能需要安装其他语言环境。比如，Perl和Ruby可能需要用户安装ActivePerl或RubyInstaller，而AWK可以通过gawk for Windows获得。此外，PowerShell也是一个可行的选项，因为它是Windows自带的强大脚本工具。 我需要分析每种可能的替代方案的可行性，并提供具体的代码示例。例如，使用Perl编写类似的转换逻辑，或者利用PowerShell的内置日期处理功能。同时，要确保这些方法在Windows环境下能够顺利运行，可能需要用户安装额外的软件，但尽量选择用户可能已经具备的环境。 另外，用户可能需要批量处理或保持时间连续性，尤其是在日志跨越系统重启的情况下，但用户的问题目前主要集中在时间格式转换上，所以先解决基础转换，再考虑其他复杂情况。 总结可能的替代方案： 1. PowerShell脚本：利用其内置的日期时间处理功能。 2. Perl脚本：使用正则表达式匹配和日期计算。 3. AWK结合其他命令行工具：可能需要搭配date命令，但Windows的date命令功能有限，可能需要使用第三方工具。 4. Ruby脚本：类似于Python的逻辑，但需要Ruby环境。 接下来需要为每种方案提供具体的实现步骤和代码示例，同时比较它们的优缺点，帮助用户选择最适合的替代方法。同时，要确保代码正确处理时间戳，补足微秒，计算基准时间，并转换格式。</think>### 替代Python脚本的解决方案 根据引用[1]中提到的多语言嵌入方法，提供以下4种替代方案，适用于Windows环境下的Kernel Log时间格式转换： --- #### **方案1：PowerShell脚本实现**（无需额外安装） ```powershell # kernel_log_convert.ps1 param($inputFile, $outputFile = "translated.log") # 获取基准时间（以第一条日志时间为准） $firstLine = Get-Content $inputFile -First 1 if ($firstLine -match '\[\s*(\d+)\.(\d+)\]') { $baseSeconds = [int]$matches[1] $baseMs = [int]($matches[2].PadRight(6, '0').Substring(0,6)) $tBoot = (Get-Date).AddSeconds(-$baseSeconds).AddMilliseconds(-($baseMs / 1000)) } # 处理所有行 Get-Content $inputFile | ForEach-Object { $_ -replace '\[\s*(\d+)\.(\d+)\]', { $sec = [int]$args[0].Groups[1].Value $mic = [int]($args[0].Groups[2].Value.PadRight(6, '0').Substring(0,6)) $logTime = $tBoot.AddSeconds($sec).AddMilliseconds($mic / 1000) "[{0:yyyy-MM-dd HH:mm:ss.fff}]" -f $logTime } } | Set-Content $outputFile ``` **使用方式**： ```cmd powershell -ExecutionPolicy Bypass -File kernel_log_convert.ps1 input.log ``` --- #### **方案2：Perl单行脚本**（需安装[Strawberry Perl](https://strawberryperl.com/)） ```perl # 命令行直接执行 perl -pe "use POSIX qw(strftime); BEGIN{ $t_boot = time() - ((split(/\./, (m/\[\s*(\d+\.\d+)\]/ ? $1 : 0)))[0]); } s/\[\s*(\d+)\.(\d+)\]/'['.strftime('%Y-%m-%d %H:%M:%S', localtime($t_boot + $1)).'.'.sprintf('%03d', $2/1000).']'/ge" input.log > translated.log ``` **原理**：通过计算系统启动时间基准，使用POSIX模块进行时间格式化[^1] --- #### **方案3：AWK + 外部命令组合**（需安装[GNUWin32 coreutils](http://gnuwin32.sourceforge.net/)） ```bash # kernel_convert.awk BEGIN { "date +%s" | getline now getline first_line < ARGV[1] if (match(first_line, /\[[[:space:]]*([0-9]+)\.([0-9]+)\]/, arr)) { base_sec = arr[1] base_micro = substr(arr[2] "000000", 1, 6) t_boot = now - base_sec - (base_micro / 1000000) } } { while (match($0, /\[[[:space:]]*([0-9]+)\.([0-9]+)\]/, arr)) { sec = arr[1] micro = substr(arr[2] "000000", 1, 6) timestamp = t_boot + sec + (micro / 1000000) cmd = "date -d @" timestamp " +\"[%Y-%m-%d %H:%M:%S.%3N]\"" cmd | getline formatted close(cmd) sub(/\[[[:space:]]*[0-9]+\.[0-9]+\]/, formatted) } print } ``` **执行命令**： ```cmd awk -f kernel_convert.awk input.log > translated.log ``` --- #### **方案4：Ruby脚本**（需安装[RubyInstaller](https://rubyinstaller.org/)） ```ruby # kernel_convert.rb input_file, output_file = ARGV[0], ARGV[1] || 'translated.log' # 获取基准时间 first_line = File.open(input_file, &:readline) if first_line =~ /\[\s*(\d+)\.(\d+)\]/ base_sec = $1.to_i base_micro = $2.ljust(6, '0')[0..5].to_i t_boot = Time.now - base_sec - base_micro.to_f / 1_000_000 end # 处理日志 File.open(output_file, 'w') do |fout| File.foreach(input_file) do |line| fout.puts line.gsub(/\[\s*(\d+)\.(\d+)\]/) { |m| sec = $1.to_i micro = $2.ljust(6, '0')[0..5].to_i log_time = t_boot + sec + micro.to_f / 1_000_000 "[#{log_time.strftime('%Y-%m-%d %H:%M:%S.%3N')}]" } end end ``` **执行命令**： ```cmd ruby kernel_convert.rb input.log ``` --- ### 方案对比 | 方案 | 依赖环境 | 性能 | 可维护性 | 时间精度处理 | |-----------|-----------------------|------|------|--------------| | PowerShell | Windows原生支持 | 中等 | 高 | 精确到毫秒 | | Perl | 需安装Perl解释器 | 高 | 中 | 支持微秒级转换 | | AWK | 需GNU工具链 | 较低 | 低 | 依赖外部date命令 | | Ruby | 需Ruby环境 | 高 | 高 | 原生时间对象支持 | --- ### 关键问题应对策略 1. **时区处理**：所有方案默认使用系统时区，可通过`Get-Date -Format o`（PowerShell）或`Time.now.utc`（Ruby）显式指定时区[^1] 2. **跨重启日志**：需记录最后一次转换时间到外部文件，下次运行时读取（需添加文件锁机制） 3. **批量处理**：使用`Get-ChildItem *.log | ForEach {...}`（PowerShell）或`Dir.glob('*.log')`（Ruby） ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值