2008年6月5日 星期四

[SQL] 搜尋金額的範圍內容

資料庫真是一項好用的東西,尤其是精通 SQL 語法的話,對於 programmer 來說,真是如虎添翼阿~~

回想起大學時代修資料庫系統的課,老師第一堂課就說:所有的程式,資料的運算如果能用 SQL 解決,就一定要用 SQL 解決,不然,你花大錢買他幹嘛!!如果只是用資料庫來存資料,取資料都只有簡單的 CRUD (Create,Replace,Update and Delete),那跟直存成檔案有啥差別!!

這句話讓我深深的體會在這次的 CASE 中。因為我這次接的 CASE 是關於撥款的系統,很多資料的運算要處哩,如果全部都讓我寫程式自己作,那效能一定會是最令人詬病的!!

回到我們的主題,光看這主題一定不能了解我要解的問題??

precondition : the database is MS SQL Server 2K

那我們就來描述一下我所遇到的問題:

如果使用者給你的 table 長這樣

Table A

range ....

------------------------------------------

0~100 ...

101~200...

...

但是使用者希望他是輸入 51,然後你就要把第一筆的資料撈給他!!

剛開始我就想,哇!! 好難~~但是,問題就是要解,那要怎樣解勒??

一步步來,首先,我們要先試試看能不能把 0~100 這種字串透過 SQL 來拆解,透過線上的文件中我們可以由 String 資料型態的相關 function 值中發現一個 function - CHARINDEX(pattern, str)

於是我們就寫下

SELECT CHARINDEX('~','0~100')

結果的確是我們要的結果,CHARINDEX function 是用來將特定的 pattern 來給與 str 中的位置,由上面的測試結果為 2 就可以知道這 function 對我們是有用的~~

接著就是要取出部分的字串,這項要求很熟悉,

沒錯要用 SUBSTRING(str,begin,len),於是我們就用它來先取出左邊的範圍吧!!

DECLARE @x AS VARCHAR

SET @x='0~100'

SELECT SUBSTRING(@x, 1, CHARINDEX('~', @x)-1)

上面的作法是為了不要每次都打 '0~100' ,所以宣告一個變數來儲存~~

上面的 SQL 語法得到的結果會是 0 ,恭喜~~我們成功了左邊的範圍。

那右邊的範圍可以如法炮製嗎?? 好像還差一個東西:我們要如何知道字串的長度勒??

答案是 LEN(str) function

所以我們來整合一下吧~~

DECLARE @x AS VARCHAR

SET @x='0~100'

SELECT SUBSTRING(@x, 1, CHARINDEX('~', @x)-1) AS leftRange,

         SUBSTRING(@x, CHARINDEX('~', @x)+1, LEN(@x)) AS rightRange

完成了一半囉!!

接下來就是要將剛剛的 SQL 語法套用到我們的問題中。

SELECT SUBSTRING(range, 1, CHARINDEX('~', range)-1) AS leftRange,

SUBSTRING(range,CHARINDEX('~',range)+1,LEN(range)) AS rightRange

FROM A

結果就是

leftRange    rightRange

-----------------------------------

0                100

101             200

...

的確這樣我們就可以拆解出範圍囉~~

如果們把這樣的結果當作 subquery,對這樣的結果作進一步的包裝,那我們就可以將使用者的要求達成囉~~ 下面假這使用者輸入的內容為 @input

DECLARE @input AS VARCHAR

SET @input='51'

SELECT T.*

FROM (

  SELECT *, SUBSTRING(range, 1, CHARINDEX('~', range)-1) AS leftRange, SUBSTRING(range,CHARINDEX('~',range)+1,LEN(range)) AS rightRange  FROM A

) AS T WHERE @input BETWEEN T.leftRange AND T.rightRange

問題得到解決了~~

回到一開始,如果這樣的問題要用程式來解決的話,勢必要花掉很多的時間,所以能用 SQL 幫我們運算,何樂而不為呢~~

以上的經驗分享之~~

1 則留言:

Toby 提到...

受益了,很感謝您的分享!