資料庫真是一項好用的東西，尤其是精通 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 幫我們運算，何樂而不為呢~~
以上的經驗分享之~~
發表文章
