2013年11月16日 星期六

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進貨A0014000
2013/10/11進貨A00210000
2013/10/11銷貨A0030-300
2013/10/12銷貨A0040-600
2013/10/13進貨A0056000
2013/10/14進貨A00621000
2013/10/14銷貨A0070-300
2013/10/15銷貨A0080-1400
2013/10/15進貨A0095000
2013/10/16進貨A01016000
2013/10/16銷貨A0110-200
2013/10/16銷貨A0120-700


日期類別單號入庫數量出庫數量累計庫存
2013/10/11進貨A0014000400
2013/10/11進貨A002100001400
2013/10/11銷貨A0030-3001100
2013/10/12銷貨A0040-600500
2013/10/13進貨A00560001100
2013/10/14進貨A006210003200
2013/10/14銷貨A0070-3002900
2013/10/15銷貨A0080-14001500
2013/10/15進貨A00950002000
2013/10/16進貨A010160003600
2013/10/16銷貨A0110-2003400
2013/10/16銷貨A0120-7002700

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

SELECT 日期, 類別, 單號, 入庫數量, 出庫數量,
  (SELECT SUM(入庫數量 + 出庫數量) FROM ALEX AS ONE
    WHERE TWO.單號 >= ONE.單號) AS 累計庫存
FROM ALEX AS TWO
看倌們喝完了茶,也可以開始試看看,或有更不錯的技巧,也觀迎與我分享。

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

2 則留言:

  1. 那如果只要算出 入庫數量 但是沒有 (Uniqu) 的欄位時要怎麼做

    回覆刪除
    回覆
    1. 基本上建議還是要有個Uniqu)作為依據,因為它代表著每個時期的編號,不過也是可以透過日期或時間欄位的條件去達到這樣的效果。

      刪除