最近看到一个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分)
创建表数据:
go
IF NOT OBJECT_ID ( ' [Contract] ' ) IS NULL
DROP TABLE [ Contract ]
GO
Create table [ Contract ]
(ID int primary key identity ( 1 , 1 )
, [ Name ] nvarchar ( 50 ) null
,Total float null
,buget Nvarchar ( 500 ) null
)
go
insert into [ Contract ]
select ' 合同名称 ' , 100 , ' 102,22 '
union all
select ' 合同名称2 ' , 300 , ' ,102,22, '
union all
select ' 合同名称3 ' , 300 , ' 101,23, '
分析:这道题乍看很简单,由于肯定用到Replace,所以很自然的结合left,right,从而得到以下语句
方法一:
update [ Contract ] set buget = buget + ' , ' where right (buget, 1 ) = ' , '
如果能写成一个 SQL语句,可以加1分。
set buget = ( case when ( left (buget, 1 ) != ' , ' and right (buget, 1 ) != ' , ' ) then ' , ' + buget + ' , '
when left (buget, 1 ) != ' , ' then ' , ' + buget
when right (buget, 1 ) != ' , ' then buget + ' , '
else buget
end )
如果能从字符串的开关和结尾这个思路出发,结合Reverse,可以提到如下方法:
方法二:
update [ Contract ] set buget = buget + ' , ' where charindex ( ' , ' , reverse (buget)) <> 1
该方法,主要涉及charindex函数和reverse函数。
说实话,我当时就这两种思路,这也是SQL中常见的基本用法。但出人意料的第三种方法出现了。
方法三:
UPDATE [ contract ] SET Buget = REPLACE (Buget, ' ,, ' , ' , ' )
解析:该方法最主要的亮点不在于语法的精妙,而在于其思路的异于常规。先给两边补上逗号,再替换双逗号为单逗号。这在实际编程中确实难能可贵。换句话说,如果没有事先思考过的话,这反映了解题者反应敏捷,思路开放。因此,至少可以再加3分。
当然,此语句其实还是有bug,比如如果原bug字段中间有两个逗号,那么在Replace时就会更新掉不应该更新的内容。不过,稍加修正,限定replace的范围即可,
受此思路启发,可以引申得到以下类似方法:
方法四:
charindex ( ' , ' ,buget) = 1
UPDATE [ contract ] SET Buget = substring (BuGet, 1 , len (BuGet) - 1 ) where
charindex ( ' , ' , reverse (buget)) = 1
UPDATE [ contract ] SET BuGet = ' , ' + BuGet + ' , '
该方法是先去掉两边的逗号,再给每条记录加上逗号,比起方法三来,稍显繁琐,这也反衬了方法三的巧妙。
当然,也可以结合前面的思路稍作修正,这里就不再赘述,请读者自己思考。
感悟:释迦牟尼说过“人生需要经过六项修炼:布施、持戒、忍辱、精进、禅定、智慧。”,SQL编程,或C#、Java,甚至Javascrip的某个领域也是如此。技术是死的,思路是鲜活的,有时候,思路能轻易地突破技术很难实现的死角。到了一定程度时,会发现潜意识里已经被惯性思维塞满,而无法接受新鲜思维方式或思路,如果一段时间内持续如此,那么,我们应该警醒,把自己的头脑放空,把自己置于一个初学者的地位,重新开始“精进”的修炼!