字段A有空也有带数值的
如何把空值的部分加上数值
要求
1、不能重复
2、按2000000000001起增加，2000000000002，2000000000003....
3、效率要高，因为字段A可能有十万行数据。
4、执行语句遇到死机等特殊情况时，能否对数据库避免产生影响。
SQL2005 谢谢！
------解决方案--------------------
- SQL code
/******************************************************************************** *主题： SQl 2008/2005 论坛问题解答 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.08.7 *Mail:[email protected] *另外：转载请著名出处。 **********************************************************************************/ --字段A有空也有带数值的--如何把空值的部分加上数值--要求--1、不能重复--2、按2000000000001起增加，2000000000002，2000000000003....--3、效率要高，因为字段A可能有十万行数据。--4、执行语句遇到死机等特殊情况时，能否对数据库避免产生影响。--SQL2005 谢谢！--输入测试数据use DBTextgoif OBJECT_ID('tb_test') is not nulldrop table tb_testgocreate table tb_test(id int identity primary key,A varchar(50))godeclare @i int;set @i=0;while @i<300beginif right (DATEPART(ms,GETDATE()),1)=0insert into tb_test values('test'+cast(@i as varchar));else insert into tb_test values(null); set @[email protected]+1 end go --查看测试数据 select * from tb_test /* id A----------- --------------------------------------------------1 NULL2 NULL3 test24 test35 test46 test57 test68 test79 test810 test911 test1012 test1113 test1214 test1315 test1416 test1517 test1618 test17....38 test3739 NULL40 NULL41 NULL42 NULL43 NULL44 NULL45 NULL46 NULL47 NULL48 NULL49 NULL........297 NULL298 NULL299 NULL300 NULL */ --按照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 20000000000012 20000000000023 20000000000034 20000000000045 20000000000056 20000000000067 test68 test79 test810 test911 200000000000712 200000000000813 200000000000914 200000000001015 200000000001116 200000000001217 200000000001318 200000000001419 200000000001520 200000000001621 200000000001722 200000000001823 200000000001924 200000000002025 200000000002126 200000000002227 200000000002328 200000000002429 200000000002530 200000000002631 200000000002732 200000000002833 200000000002934 200000000003035 200000000003136 200000000003237 200000000003338 200000000003439 200000000003540 200000000003641 200000000003742 200000000003843 200000000003944 200000000004045 test4446 test4547 test4648 test4749 test4850 test4951 test5052 test5153 test5254 test5355 test5456 test5557 test5658 test5759 test5860 test5961 test6062 test6163 test6264 test6365 test6466 200000000004167 200000000004268 200000000004369 200000000004470 200000000004571 200000000004672 200000000004773 200000000004874 200000000004975 200000000005076 200000000005177 200000000005278 test7779 test7880 test7981 test8082 test8183 test8284 test8385 test8486 test8587 test8688 200000000005389 200000000005490 200000000005591 200000000005692 200000000005793 200000000005894 200000000005995 200000000006096 200000000006197 200000000006298 200000000006399 2000000000064100 test99101 test100102 test101103 test102104 test103105 test104106 test105107 test106108 test107109 test108110 test109111 test110112 test111113 test112114 test113115 test114116 test115117 test116118 test117119 test118120 test119121 test120122 test121123 test122124 test123125 test124126 test125127 test126128 test127129 test128130 test129131 test130132 test131133 test132134 test133135 test134136 test135137 test136138 test137139 2000000000065140 2000000000066141 2000000000067142 2000000000068143 2000000000069144 2000000000070145 2000000000071146 2000000000072147 2000000000073148 2000000000074149 2000000000075150 2000000000076151 2000000000077152 2000000000078153 2000000000079154 2000000000080155 2000000000081156 2000000000082157 2000000000083158 2000000000084159 2000000000085160 2000000000086161 2000000000087162 2000000000088163 test162164 test163165 test164166 test165167 test166168 test167169 test168170 test169171 test170172 test171173 test172174 test173175 test174176 test175177 test176178 test177179 test178180 test179181 test180182 test181183 test182184 test183185 test184186 test185187 2000000000089188 2000000000090189 2000000000091190 2000000000092191 2000000000093192 2000000000094193 2000000000095194 2000000000096195 2000000000097196 2000000000098197 2000000000099198 2000000000100199 2000000000101200 2000000000102201 2000000000103202 2000000000104203 2000000000105204 2000000000106205 2000000000107206 2000000000108207 2000000000109208 2000000000110209 2000000000111210 2000000000112211 2000000000113212 2000000000114213 2000000000115214 2000000000116215 2000000000117216 2000000000118217 2000000000119218 2000000000120219 2000000000121220 2000000000122221 2000000000123222 2000000000124223 2000000000125224 2000000000126225 2000000000127226 2000000000128227 2000000000129228 2000000000130229 2000000000131230 2000000000132231 2000000000133232 2000000000134233 test232234 test233235 test234236 test235237 test236238 test237239 test238240 test239241 test240242 test241243 test242244 test243245 test244246 test245247 test246248 test247249 test248250 test249251 test250252 test251253 test252254 test253255 test254256 2000000000135257 2000000000136258 2000000000137259 2000000000138260 2000000000139261 2000000000140262 2000000000141263 2000000000142264 2000000000143265 2000000000144266 2000000000145267 2000000000146268 2000000000147269 2000000000148270 2000000000149271 2000000000150272 2000000000151273 2000000000152274 2000000000153275 2000000000154276 2000000000155277 2000000000156278 2000000000157279 2000000000158280 test279281 test280282 test281283 test282284 test283285 test284286 test285287 test286288 test287289 test288290 test289291 test290292 test291293 test292294 test293295 test294296 test295297 test296298 test297299 test298300 test299(300 行受影响) */ *作者：Stephenzhou(阿蒙) *日期: 2012.08.6 *Mail:[email protected] *另外：转载请著名出处。 *博客地址：http://blog.csdn.net/szstephenzhou