把字段为null变成指定连续的数据 论坛问题处理

原论坛贴子地址：点击打开链接

   

 /********************************************************************************           
 *主题： SQl 2008/2005 论坛问题解答           
 *说明：本文是个人学习的一些笔记和个人愚见            
 *      有很多地方你可能觉得有异议，欢迎一起讨论            
            
 *作者：Stephenzhou(阿蒙)            
 *日期: 2012.08.7          
 *Mail:szstephenzhou@163.com            
 *另外：转载请著名出处。            
**********************************************************************************/ 

 
--字段A有空也有带数值的
--如何把空值的部分加上数值
--要求
--1、不能重复
--2、按2000000000001起增加，2000000000002，2000000000003....
--3、效率要高，因为字段A可能有十万行数据。
--4、执行语句遇到死机等特殊情况时，能否对数据库避免产生影响。

--SQL2005 谢谢！

 

--输入测试数据

use DBText
go
if OBJECT_ID('tb_test') is not null
drop table tb_test
go

create table tb_test(id int identity primary key,A varchar(50))
go

declare @i int;
set @i=0;
while @i<300
begin
if right (DATEPART(ms,GETDATE()),1)=0
insert into tb_test values('test'+cast(@i as varchar));
else
 insert into tb_test values(null);
 set @i=@i+1
 end
 go
   --查看测试数据
   select * from tb_test
  
   /*
   id          A
----------- --------------------------------------------------
1           test0
2           test1
3           test2
4           test3
5           test4
6           test5
7           NULL
8           NULL
9           NULL
10          NULL
11          NULL
12          NULL
13          NULL
14          NULL
15          NULL
16          NULL
17          NULL
18          NULL
19          NULL
20          NULL
21          NULL
22          NULL
23          NULL
24          NULL
25          test24
26          test25
27          test26
28          test27
29          test28
30          test29
31          test30
32          test31
33          test32
34          test33
35          test34
36          test35
37          test36
38          test37
39          test38
40          test39
41          test40
42          test41
43          test42
44          test43
45          test44
46          test45
47          test46
48          test47
49          test48
50          NULL
51          NULL
52          NULL
53          NULL
54          NULL
55          NULL
56          NULL
57          NULL
58          NULL
59          NULL
60          NULL
61          NULL
62          NULL
63          NULL
64          NULL
65          NULL
66          NULL
67          NULL
68          test67
...
.........
   */
  
   --按照LZ说的情况更新
  
   declare @str varchar(50)
   set @str='2000000000000'
   update tb_test    set   A=m.Ae from
   (
   select t.id as id ,t.A as A ,f.id as fid ,f.A as fA ,f.Ae as Ae from tb_test t left join (
   select * ,Ae=(left(@str,len(@str)-LEN(s.rn))+CAST(s.rn as varchar)) from
   (  select * ,ROW_NUMBER()over(partition by A order by id) as rn from tb_test where A is null )s   )f on f.id=t.id)m
  
    where    tb_test.A is null and  tb_test.id=m.id
   
   
    ---输出更新后的结果集合
    select * from tb_test
    /*
    id          A
----------- --------------------------------------------------
1           test0
2           test1
3           test2
4           test3
5           test4
6           test5
7           2000000000001
8           2000000000002
9           2000000000003
10          2000000000004
11          2000000000005
12          2000000000006
13          2000000000007
14          2000000000008
15          2000000000009
16          2000000000010
17          2000000000011
18          2000000000012
19          2000000000013
20          2000000000014
21          2000000000015
22          2000000000016
23          2000000000017
24          2000000000018
25          test24
26          test25
27          test26
28          test27
29          test28
30          test29
31          test30
32          test31
33          test32
34          test33
35          test34
36          test35
37          test36
38          test37
39          test38
40          test39
41          test40
42          test41
43          test42
44          test43
45          test44
46          test45
47          test46
48          test47
49          test48
50          2000000000019
51          2000000000020
52          2000000000021
53          2000000000022
54          2000000000023
55          2000000000024
56          2000000000025
57          2000000000026
58          2000000000027
59          2000000000028
60          2000000000029
61          2000000000030
62          2000000000031
63          2000000000032
64          2000000000033
65          2000000000034
66          2000000000035
67          2000000000036
68          test67
69          test68
70          test69
71          test70
72          test71
73          test72
74          test73
75          test74
76          test75
77          test76
78          test77
79          test78
80          test79
81          test80
82          test81
83          test82
84          test83
85          test84
86          test85
87          test86
88          test87
89          test88
90          test89
91          test90
92          test91
93          test92
94          test93
95          test94
96          test95
97          test96
98          2000000000037
99          2000000000038
100         2000000000039
101         2000000000040
102         2000000000041
103         2000000000042
104         2000000000043
105         2000000000044
106         2000000000045
107         2000000000046
108         2000000000047
109         2000000000048
110         2000000000049
111         2000000000050
112         2000000000051
113         2000000000052
114         2000000000053
115         2000000000054
116         2000000000055
117         2000000000056
118         2000000000057
119         2000000000058
120         2000000000059
121         2000000000060
122         2000000000061
123         test122
124         test123
125         test124
126         test125
127         test126
128         test127
129         test128
130         test129
131         test130
132         test131
133         test132
134         test133
135         test134
136         test135
137         test136
138         test137
139         test138
140         test139
141         test140
142         test141
143         test142
144         test143
145         2000000000062
146         2000000000063
147         2000000000064
148         2000000000065
149         2000000000066
150         2000000000067
151         2000000000068
152         2000000000069
153         2000000000070
154         2000000000071
155         2000000000072
156         2000000000073
157         2000000000074
158         2000000000075
159         2000000000076
160         2000000000077
161         2000000000078
162         2000000000079
163         2000000000080
164         2000000000081
165         2000000000082
166         2000000000083
167         2000000000084
168         2000000000085
169         2000000000086
170         test169
171         test170
172         test171
173         test172
174         test173
175         test174
176         test175
177         test176
178         test177
179         test178
180         test179
181         test180
182         test181
183         test182
184         test183
185         test184
186         test185
187         test186
188         test187
189         test188
190         test189
191         test190
192         test191
193         test192
194         test193
195         2000000000087
196         2000000000088
197         2000000000089
198         2000000000090
199         2000000000091
200         2000000000092
201         2000000000093
202         2000000000094
203         2000000000095
204         2000000000096
205         2000000000097
206         2000000000098
207         2000000000099
208         2000000000100
209         2000000000101
210         2000000000102
211         2000000000103
212         2000000000104
213         2000000000105
214         2000000000106
215         2000000000107
216         2000000000108
217         2000000000109
218         2000000000110
219         2000000000111
220         2000000000112
221         2000000000113
222         2000000000114
223         2000000000115
224         2000000000116
225         2000000000117
226         2000000000118
227         2000000000119
228         2000000000120
229         2000000000121
230         2000000000122
231         2000000000123
232         2000000000124
233         2000000000125
234         2000000000126
235         2000000000127
236         2000000000128
237         2000000000129
238         2000000000130
239         2000000000131
240         2000000000132
241         2000000000133
242         2000000000134
243         2000000000135
244         2000000000136
245         2000000000137
246         2000000000138
247         2000000000139
248         2000000000140
249         2000000000141
250         2000000000142
251         2000000000143
252         2000000000144
253         2000000000145
254         2000000000146
255         test254
256         test255
257         test256
258         test257
259         test258
260         test259
261         test260
262         test261
263         test262
264         test263
265         test264
266         test265
267         test266
268         test267
269         test268
270         test269
271         test270
272         test271
273         test272
274         test273
275         test274
276         test275
277         test276
278         test277
279         test278
280         test279
281         test280
282         test281
283         test282
284         test283
285         test284
286         test285
287         test286
288         test287
289         test288
290         test289
291         test290
292         test291
293         test292
294         test293
295         test294
296         test295
297         test296
298         test297
299         test298
300         test299

(300 行受影响)
    */
   
  *作者：Stephenzhou(阿蒙)     
 *日期: 2012.08.6
 *Mail:szstephenzhou@163.com     
 *另外：转载请著名出处。
 *博客地址：http://blog.csdn.net/szstephenzhou