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 go122 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 go225 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 go328 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] off345 346 347 --输入任何一个日期，查找其之前和之后的连续的日期序列348 create function func_continuity(@d_start_date date)--+1时点之后的日期序列，-1时点之前的日期序列349 returns table350 as351 return352 (353 with cte_end 354 as355 (356 select i_id,d_date357 from dbo.liu_shui 358 where d_date=@d_start_date 359 union all360 select a.i_id,a.d_date361 from dbo.liu_shui a362 inner join cte_end as b363 on datediff(day,b.d_date,a.d_date)=1--时点之后的日期序列364 )365 ,cte_begin366 as367 (368 select i_id,d_date369 from dbo.liu_shui 370 where d_date=@d_start_date 371 union all372 select a.i_id,a.d_date373 from dbo.liu_shui a374 inner join cte_begin as b375 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 go382 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 a387 cross apply func_continuity(a.d_date) as b388 ) as tt389 group by i_id,d_date 390 having count(b_d_date)>=3--大于等于几就是连续多少天及以上发生391 order by d_date