最近看到一个SQL Server的小例子，发现完全可以作为SQL server的一道入门面试题。题目如下：
例：有一合同表Contract
| Id | Name | Total |
buget |
| 1 | 合同名称 | 100 | 102,22 |
| 2 | 合同名称2 | 300 | ,102,22, |
| 3 | 合同名称3 | 200 | 103,23, |
要求:用SQL语句更新表的buget字段，如果前后没有","要加上","（即一个英文逗号）。（10分）
创建表数据：
View Code
|
1 |
<ol class="dp-sql"><li class="alt"><span><span>use Testdb2 </span></span></li><li><span>go </span></li><li class="alt"><span>IF </span><span class="op"><font color="#808080">NOT</font></span><span> OBJECT_ID(</span><span class="string"><font color="#0000ff">'[Contract]'</font></span><span>) </span><span class="keyword"><strong><font color="#006699">IS</font></strong></span><span> </span><span class="op"><font color="#808080">NULL</font></span><span> </span></li><li><span class="keyword"><strong><font color="#006699">DROP</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">TABLE</font></strong></span><span> [Contract] </span></li><li class="alt"><span>GO </span></li><li><span class="keyword"><strong><font color="#006699">Create</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">table</font></strong></span><span> [Contract] </span></li><li class="alt"><span>(ID </span><span class="keyword"><strong><font color="#006699">int</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">primary</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">key</font></strong></span><span> identity(1,1) </span></li><li><span>,[</span><span class="keyword"><strong><font color="#006699">Name</font></strong></span><span>] nvarchar(50) </span><span class="op"><font color="#808080">null</font></span><span> </span></li><li class="alt"><span>,Total </span><span class="keyword"><strong><font color="#006699">float</font></strong></span><span> </span><span class="op"><font color="#808080">null</font></span><span> </span></li><li><span>,buget Nvarchar(500) </span><span class="op"><font color="#808080">null</font></span><span> ) </span></li><li class="alt"><span>go </span></li><li><span class="keyword"><strong><font color="#006699">insert</font></strong></span><span> </span><span class="keyword"><strong><font color="#006699">into</font></strong></span><span> [Contract] </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="string"><font color="#0000ff">'合同名称'</font></span><span>, 100,</span><span class="string"><font color="#0000ff">'102,22'</font></span><span> </span></li><li><span class="keyword"><strong><font color="#006699">union</font></strong></span><span> </span><span class="op"><font color="#808080">all</font></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="string"><font color="#0000ff">'合同名称2'</font></span><span>, 300,</span><span class="string"><font color="#0000ff">',102,22,'</font></span><span> </span></li><li><span class="keyword"><strong><font color="#006699">union</font></strong></span><span> </span><span class="op"><font color="#808080">all</font></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">select</font></strong></span><span> </span><span class="string"><font color="#0000ff">'合同名称3'</font></span><span>, 300,</span><span class="string"><font color="#0000ff">'101,23,'</font></span><span> </span></li></ol> |
方法一：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> [Contract] </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> buget=</span><span class="string"><font color="#0000ff">','</font></span><span>+buget </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> </span><span class="func"><font color="#ff1493">left</font></span><span>(buget,1)=</span><span class="string"><font color="#0000ff">','</font></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> [Contract] </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> buget=buget+</span><span class="string"><font color="#0000ff">','</font></span><span> </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> </span><span class="func"><font color="#ff1493">right</font></span><span>(buget,1)=</span><span class="string"><font color="#0000ff">','</font></span><span> </span></li></ol> |
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> [Contract] </span></span></li><li><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> buget=(</span><span class="func"><font color="#ff1493">case</font></span><span> </span><span class="keyword"><strong><font color="#006699">when</font></strong></span><span> (</span><span class="func"><font color="#ff1493">left</font></span><span>(buget,1)!=</span><span class="string"><font color="#0000ff">','</font></span><span> </span><span class="op"><font color="#808080">and</font></span><span> </span><span class="func"><font color="#ff1493">right</font></span><span> (buget,1)!=</span><span class="string"><font color="#0000ff">','</font></span><span>) </span><span class="keyword"><strong><font color="#006699">then</font></strong></span><span> </span><span class="string"><font color="#0000ff">','</font></span><span>+buget+</span><span class="string"><font color="#0000ff">','</font></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">when</font></strong></span><span> </span><span class="func"><font color="#ff1493">left</font></span><span>(buget,1)!=</span><span class="string"><font color="#0000ff">','</font></span><span> </span><span class="keyword"><strong><font color="#006699">then</font></strong></span><span> </span><span class="string"><font color="#0000ff">','</font></span><span>+buget </span></li><li><span class="keyword"><strong><font color="#006699">when</font></strong></span><span> </span><span class="func"><font color="#ff1493">right</font></span><span>(buget,1)!=</span><span class="string"><font color="#0000ff">','</font></span><span> </span><span class="keyword"><strong><font color="#006699">then</font></strong></span><span> buget+</span><span class="string"><font color="#0000ff">','</font></span><span> </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">else</font></strong></span><span> buget </span></li><li><span class="keyword"><strong><font color="#006699">end</font></strong></span><span>) </span></li></ol> |
方法二：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> [Contract] </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> buget=</span><span class="string"><font color="#0000ff">','</font></span><span>+buget </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> charindex(</span><span class="string"><font color="#0000ff">','</font></span><span>,buget)<>1 </span></span></li><li><span class="keyword"><strong><font color="#006699">update</font></strong></span><span> [Contract] </span><span class="keyword"><strong><font color="#006699">set</font></strong></span><span> buget=buget+</span><span class="string"><font color="#0000ff">','</font></span><span> </span><span class="keyword"><strong><font color="#006699">where</font></strong></span><span> charindex(</span><span class="string"><font color="#0000ff">','</font></span><span>,reverse(buget))<>1 </span></li></ol> |
说实话，我当时就这两种思路，这也是SQL中常见的基本用法。但出人意料的第三种方法出现了。
方法三：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">UPDATE</font></strong></span><span> [contract] </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> Buget = </span><span class="string"><font color="#0000ff">','</font></span><span>+Buget+</span><span class="string"><font color="#0000ff">','</font></span><span> </span></span></li><li><span class="keyword"><strong><font color="#006699">UPDATE</font></strong></span><span> [contract] </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> Buget = </span><span class="func"><font color="#ff1493">REPLACE</font></span><span>(Buget,</span><span class="string"><font color="#0000ff">',,'</font></span><span>,</span><span class="string"><font color="#0000ff">','</font></span><span>) </span></li></ol> |
当然，此语句其实还是有bug,比如如果原bug字段中间有两个逗号，那么在Replace时就会更新掉不应该更新的内容。不过，稍加修正，限定replace的范围即可，
受此思路启发，可以引申得到以下类似方法：
方法四：
|
1 |
<ol class="dp-sql"><li class="alt"><span><span class="keyword"><strong><font color="#006699">UPDATE</font></strong></span><span> [contract] </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> Buget = </span><span class="func"><font color="#ff1493">substring</font></span><span>(BuGet,2,len(BuGet)-1) wherecharindex(</span><span class="string"><font color="#0000ff">','</font></span><span>,buget)=1 </span></span></li><li><span class="keyword"><strong><font color="#006699">UPDATE</font></strong></span><span> [contract] </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> Buget = </span><span class="func"><font color="#ff1493">substring</font></span><span>(BuGet,1,len(BuGet)-1) wherecharindex(</span><span class="string"><font color="#0000ff">','</font></span><span>,reverse(buget))=1 </span></li><li class="alt"><span class="keyword"><strong><font color="#006699">UPDATE</font></strong></span><span> [contract] </span><span class="keyword"><strong><font color="#006699">SET</font></strong></span><span> BuGet = </span><span class="string"><font color="#0000ff">','</font></span><span>+BuGet+</span><span class="string"><font color="#0000ff">','</font></span><span> </span></li></ol> |
当然，也可以结合前面的思路稍作修正，这里就不再赘述，请读者自己思考。
感悟：释迦牟尼说过“人生需要经过六项修炼：布施、持戒、忍辱、精进、禅定、智慧。”，SQL编程，或C#、Java，甚至Javascrip的某个领域也是如此。技术是死的，思路是鲜活的，有时候，思路能轻易地突破技术很难实现的死角。到了一定程度时，会发现潜意识里已经被惯性思维塞满，而无法接受新鲜思维方式或思路，如果一段时间内持续如此，那么，我们应该警醒，把自己的头脑放空，把自己置于一个初学者的地位，重新开始“精进”的修炼！
原文链接：http://www.cnblogs.com/downmoon/archive/2011/03/02/1968615.html