SQL server的一道入門面試題背後的思考

最近看到一個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

use Testdb2
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 left(buget,1)=','
update [Contract] set buget=buget+',' where right(buget,1)=',' 

如果能寫成一個 SQL語句，可以加1分。

update [Contract]
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(',',buget)<>1
update [Contract] set buget=buget+',' where charindex(',',reverse(buget))<>1 

該方法，主要涉及charindex函數和reverse函數。

說實話，我當時就這兩種思路，這也是SQL中常見的基本用法。但出人意料的第三種方法出現了。

方法三：

UPDATE [contract] SET Buget = ','+Buget+','
UPDATE [contract] SET Buget = REPLACE(Buget,',,',',') 

解析：該方法最主要的亮點不在於文法的精妙，而在於其思路的異於常規。先給兩邊補上逗號，再替換雙逗號為單逗號。這在實際編程中確實難能可貴。換句話說，如果沒有事先思考過的話，這反映瞭解題者反應敏捷，思路開放。因此，至少可以再加3分。

當然，此語句其實還是有bug,比如如果原bug欄位中間有兩個逗號，那麼在Replace時就會更新掉不應該更新的內容。不過，稍加修正，限定replace的範圍即可，

受此思路啟發，可以引申得到以下類似方法：

方法四：

UPDATE [contract] SET Buget = substring(BuGet,2,len(BuGet)-1) where
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的某個領域也是如此。技術是死的，思路是鮮活的，有時候，思路能輕易地突破技術很難實現的死角。到了一定程度時，會發現潛意識裡已經被慣性思維塞滿，而無法接受新鮮思維方式或思路，如果一段時間內持續如此，那麼，我們應該警醒，把自己的頭腦放空，把自己置於一個初學者的地位，重新開始“精進”的修鍊！