[SQL] SQL Server 中如何知道某個 table 是否存在

今天剛好碰到這樣的問題，以往我都是在既有的 tables 下對資料庫進行存取，不過因為系統中出現了暫存 table，所以我在每一次執行完成後就必須刪除(drop)該 table，也因為如此，我在 SQL 終究必須先判斷該 table 是否存在，如果存在就代表上次的作業沒有將此 table 刪除！
由於上述的原因，透過 Google 找到了相關的資源：

How do I determine if a table exists in a SQL Server database?

這樣的 SQL 撰寫起來蠻容易的：

IF EXISTS(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
  AND TABLE_NAME='temp_table'
)
--如果存在在
DROP TABLE temp_table
ELSE
--如果不存在在

上述的 SQL 應該還蠻容易瞭解的，透過 IF...ELSE 敘述並且配合 EXISTS 語法就可以用來判斷存在性，整個 SQL 語法中的核心在於 SELECT 語句中，我們在 SQL Server 中是要透過 INFORMATION_SCHEMA 這個 system view 可以得知系統的中繼資料，透過 TABLES 就可以取得所有的 tables，然後我們在 WHERE 條件終將 TABLE_TYPE 設定為 BASE TABLE，這代表我們要查詢的是基本的 table，而不是 view；然後在告知 TABLE_NAME 為我們想要取得的 table 名稱，這樣就可以！至於為何是 SELECT 1 呢？這個數字 1 跟 C 語言中的 true 是類似的，因為在 SQL 中不存在 true 這樣的 boolean type，所以如果我們的 WHERE 條件成立，就會回傳 1 告知 EXISTS 為 true！

今天學到的東西，與大家分享之～

[SQL] Table Rotation(資料表旋轉)

看到主題你多少會感到很酷！因為 table 也可以旋轉喔？那麼神奇～

沒錯，如果你經常對於 table 要下 query，偏偏 table 又不是你設計(如果是你設計的那就活該啦XD)，你可能會看到項這種的 table：

id	type	value
王大名	A	100
王大名	B	50
李大砲	A	40
李大砲	C	60
陳一二	D	20

 

這時候你想要整理出一個 table 如下：

id	A	B	C	D
王大名	100	50	NULL	NULL
李大砲	40	NULL	60	NULL
陳一二	NULL	NULL	NULL	20

 

這時候你就會需要 table rotation 囉！有沒有一點感覺啦～由上面的 table 要改成下面的 table，就好像資料表做了旋轉的感覺。

回歸到 SQL 語法層面，我們該怎樣去作勒？若你是用 MS SQL 2005 那你可以鬆一口氣了！因為他有新的語法可以支援這樣的 table rotation - PIVOT and UNPIVOT。不過這裡不討論這兩種語法，我們要用最純粹的、最傳統、最正港的 SQL 語法來兜。

先假設第一個 table 就做 table Test，所以我們要取出成第二個 table 的樣式，所以我們在 SELECT 中一定會出現 id,A,B,C,D 的東西：

SELECT id,A,B,C,D

FROM test

因為 id 本來舊屬於 test Table 的資料，所以我們的目標就會在 A,B,C,D 這四個 column。如果我們要取出 column A 那我們就是要去取 test Table 中 type=A 的資料，所以目標很清楚，我們要在上面的 SQL 中在加入 sub-select：

SELECT id,

  A=(SELECT value FROM test WHERE type='A'),...

FROM test

這樣看似完成了，不過還是差一點，因為我們並沒有指定說是要誰的 A 值！所以要將其改成

SELECT T.id,

  A = (SELECT value FROM test WHERE type='A' AND id=T.id),

  B = (SELECT value FROM test WHERE type='B' AND id=T.id),..

FROM test AS T

GROUP BY T.id

哇！這樣就完成了～其實思考的過程並不難，重點是要知道 sub-select 的運用。

[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 幫我們運算，何樂而不為呢~~

以上的經驗分享之~~