T-SQL Basic Lesson 4 - 欄位

最近在玩T-SQL時，就會想起以前玩MySQL時，對DB是一種敬畏感。因為數據，在企業裡是一種非常敏感的名詞，稍有不慎，就有如排山倒海的巨浪，一發不可收拾。武俠有云：「江湖一點訣」所以在這分享一些欄位方面的小技巧，各看倌隨著我邊品茶邊看我道來。

1.若一個Table 已存在且有大量資料，若要新增一個欄位時千萬不要使用[SQL Managerment Studio ]來新增，否則會造成系統處理大量資料而鎖住，應該利用T-SQL語法處理較為恰當

T-SQL：ALTER TABLE [我的資料表名稱] ADD [欄位名稱] [型態]

範例：

ALTER TABLE EmpList ADD EmpID varchar(10)



2.若要變更資料欄位型態時(但是變更欄位型態時有許多限制，詳細資料可參考MSDN):

T-SQL：ALTER TABLE [資料表名稱] ALTER COLUMN [欄位名稱] [型態]

範例：

ALTER TABLE EmpList ALTER COLUMN EmpID int

3.T-SQL輸出時將多筆欄位盒併成單一欄位輸出
例：
#  mId
1  A
2  B
3  C
4  D

輸出變成
# finaldata
1 A,B,C,D

T-SQL語法

select left(O.datalist,len(O.datalist)-1) as finaldata from (select (SELECT cast(T.mId as nvarchar(3)) + ',' from (select x.mId from [dbo].[mTable] as x) T

FOR XML PATH('')) as 'datalist') O

4.用變數來取代欄位名
有一個 Table1 的欄位為 

List char(1)

Month01 int

Month02 int

.

.

Month11 int

Month12 int

範例：

DECLARE @M VARCHAR(2)

SET @M = '02'

DECLARE @Condition VARCHAR(20)

SET @Condition = 'A'

DECLARE @Values INT

SET @Values = 500

DECLARE @TSQL NVARCHAR(4000)

SET @TSQL = 'UPDATE Table1 '+

       'SET Month'+@M+' = '+CAST(@Values AS VARCHAR)+' '+

       'WHERE List = '+''''+@Condition+''''

EXEC SP_EXECUTESQL @TSQL

5.使用 T-SQL 語法查出某個欄位的累積值

如下所示，透過 T-SQL 指令計算出「累計庫存」數量的累積值

日期	類別	單號	入庫數量	出庫數量
2013/10/11	進貨	A001	400	0
2013/10/11	進貨	A002	1000	0
2013/10/11	銷貨	A003	0	-300
2013/10/12	銷貨	A004	0	-600
2013/10/13	進貨	A005	600	0
2013/10/14	進貨	A006	2100	0
2013/10/14	銷貨	A007	0	-300
2013/10/15	銷貨	A008	0	-1400
2013/10/15	進貨	A009	500	0
2013/10/16	進貨	A010	1600	0
2013/10/16	銷貨	A011	0	-200
2013/10/16	銷貨	A012	0	-700

日期	類別	單號	入庫數量	出庫數量	累計庫存
2013/10/11	進貨	A001	400	0	400
2013/10/11	進貨	A002	1000	0	1400
2013/10/11	銷貨	A003	0	-300	1100
2013/10/12	銷貨	A004	0	-600	500
2013/10/13	進貨	A005	600	0	1100
2013/10/14	進貨	A006	2100	0	3200
2013/10/14	銷貨	A007	0	-300	2900
2013/10/15	銷貨	A008	0	-1400	1500
2013/10/15	進貨	A009	500	0	2000
2013/10/16	進貨	A010	1600	0	3600
2013/10/16	銷貨	A011	0	-200	3400
2013/10/16	銷貨	A012	0	-700	2700

資料表欄位「單號」是獨一的（Unique），且後一個單號的值會比前一個單號的值還大，因此透過這個欄位，我們可以使用下面的 T-SQL 指令動態計算出「累計庫存」的數值：

SELECT 日期, 類別, 單號, 入庫數量, 出庫數量,

  (SELECT SUM(入庫數量 + 出庫數量) FROM ALEX AS ONE

    WHERE TWO.單號 >= ONE.單號) AS 累計庫存

FROM ALEX AS TWO

看倌們喝完了茶，也可以開始試看看，或有更不錯的技巧，也觀迎與我分享。

-雲遊山水為知已逍遙一生而忘齡- 電腦神手