資料處理的SQL小技巧

參考資料(簡體)、參考資料、參考資料

取某年特定日期，DATEPART函數

時間取到今年的特定日期，重點在紅字，要設定取到對的參數
SELECT CONVERT(DATETIME, CONVERT(NVARCHAR(10), DATEPART(YYYY,GETDATE()))+'/08/1')

同理，這樣可以取到明年的8/1，記得算括弧的數量
SELECT CONVERT(DATETIME, CONVERT(NVARCHAR(10), DATEPART(YYYY,GETDATE())+1)+'/08/1')

左右補0

表示從右方'100'開始取三位，不足的由'000'補上
SELECT RIGHT('000'+CAST('100' AS VARCHAR),3)

表示從左方'xxxxx'開始取五位，不足的由'20'補上
SELECT LEFT('xxxxx'+CAST('20' AS VARCHAR),5)  

ROW_NUMBER用法

加上排序功能，可以放多個欄位判定
SELECT ROW_NUMBER() OVER(PARTITION BY DEPT_NO, B ORDER BY SALARY DESC) ROW_NUM

轉型

SELECT CAST ( A AS NVARCHAR(4) )
SELECT CONVERT( NVARCHAR(40) ,GETDATE())

IF...ELSE IF..

用法

IF (判斷式)
   PRINT  '只有一行不用區塊包起來';
ELSE IF  (判斷式)


IF (判斷式)
   BEGIN
   PRINT  '有好多行要用區塊包起來';
   UPDATE........
   RETURN;--加了才會跳出去
   END
ELSE IF  (判斷式)

時間函數記錄

加月(今天日期加兩個月)
DATEADD(M,2,GETDATE());

減月(今天日期減一個月)
DATEADD(M,-1,GETDATE());

加日(今天日期加兩十天)
DATEADD(D,20,GETDATE());
減日(今天日期減兩十天)
DATEADD(D,-20,GETDATE());

PRIMARY KEY違反條件約束'PK_*'。無法在物件'*'上插入重複索引鍵

要插入的資料，主鍵已經存在了，重新命名主鍵再插入即可

當 IDENTITY_INSERT 設為 OFF 時，無法將外顯值插入資料表** 的識別欄位中。

通常發生在新增時，新增資料時某一欄位會自動填入..不需輸入而輸入就會這樣
範例長這樣

假設xxx是自動累加的欄位，以下這個語法就會報錯
INSERT INTO A(zzz,xxx,ccc,vvv)
VALUES('a',3,5,'dd')

改成這樣即可

INSERT INTO A(zzz,ccc,vvv)
VALUES('a',5,'dd')

SQL日期格式

原文出處

SELECT CONVERT(VARCHAR(100), GETDATE(), 0)	05 16 2006 10:57AM
SELECT CONVERT(VARCHAR(100), GETDATE(), 1)	05/16/06
SELECT CONVERT(VARCHAR(100), GETDATE(), 2)	06.05.16
SELECT CONVERT(VARCHAR(100), GETDATE(), 3)	16/05/06
SELECT CONVERT(VARCHAR(100), GETDATE(), 4)	16.05.06
SELECT CONVERT(VARCHAR(100), GETDATE(), 5)	16-05-06
SELECT CONVERT(VARCHAR(100), GETDATE(), 6)	16 05 06
SELECT CONVERT(VARCHAR(100), GETDATE(), 7)	05 16, 06
SELECT CONVERT(VARCHAR(100), GETDATE(), 8)	10:57:46
SELECT CONVERT(VARCHAR(100), GETDATE(), 9)	05 16 2006 10:57:46:827AM
SELECT CONVERT(VARCHAR(100), GETDATE(), 10)	05-16-06
SELECT CONVERT(VARCHAR(100), GETDATE(), 11)	06/05/16
SELECT CONVERT(VARCHAR(100), GETDATE(), 12)	060516
SELECT CONVERT(VARCHAR(100), GETDATE(), 13)	16 05 2006 10:57:46:937
SELECT CONVERT(VARCHAR(100), GETDATE(), 14)	10:57:46:967
SELECT CONVERT(VARCHAR(100), GETDATE(), 20)	2006-05-16 10:57:47
SELECT CONVERT(VARCHAR(100), GETDATE(), 21)	2006-05-16 10:57:47.157
SELECT CONVERT(VARCHAR(100), GETDATE(), 22)	05/16/06 10:57:47 AM
SELECT CONVERT(VARCHAR(100), GETDATE(), 23)	2006-05-16
SELECT CONVERT(VARCHAR(100), GETDATE(), 24)	10:57:47
SELECT CONVERT(VARCHAR(100), GETDATE(), 25)	2006-05-16 10:57:47.250
SELECT CONVERT(VARCHAR(100), GETDATE(), 100)	05 16 2006 10:57AM
SELECT CONVERT(VARCHAR(100), GETDATE(), 101)	05/16/2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 102)	2006.05.16
SELECT CONVERT(VARCHAR(100), GETDATE(), 103)	16/05/2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 104)	16.05.2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 105)	16-05-2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 106)	16 05 2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 107)	05 16, 2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 108)	10:57:49
SELECT CONVERT(VARCHAR(100), GETDATE(), 109)	05 16 2006 10:57:49:437AM
SELECT CONVERT(VARCHAR(100), GETDATE(), 110)	05-16-2006
SELECT CONVERT(VARCHAR(100), GETDATE(), 111)	2006/05/16
SELECT CONVERT(VARCHAR(100), GETDATE(), 112)	20060516
SELECT CONVERT(VARCHAR(100), GETDATE(), 113)	16 05 2006 10:57:49:513
SELECT CONVERT(VARCHAR(100), GETDATE(), 114)	10:57:49:547
SELECT CONVERT(VARCHAR(100), GETDATE(), 120)	2006-05-16 10:57:49
SELECT CONVERT(VARCHAR(100), GETDATE(), 121)	2006-05-16 10:57:49.700
SELECT CONVERT(VARCHAR(100), GETDATE(), 126)	2006-05-16T10:57:49.827
SELECT CONVERT(VARCHAR(100), GETDATE(), 131)	18/04/1427 10:57:49:920AM

SQL遇到的一些小問題

是說我們在下SQL陳述式的時候，有時候會把算出來的資料表放到另一個資料表.....
然後就會發生

遺漏了物件或資料行的名稱，或名稱是空的。針對 SELECT INTO 陳述式，請確定每個資料行都有名稱。至於其他陳述式，請尋找別名名稱是空的項目。不允許別名定義為 "" 或 []。請將別名變更成有效的名稱。

這時候大家的心情一定很想罵歐幹不舒服，解決辦法就是檢查一下有沒有欄位沒取到名字
還是不懂就看我舉例吧

從字元字串轉換到 uniqueidentifier 時失敗

報此錯原因是該欄位預設只能吃GUID屬性的值

如果只是要製造假資料，可以用這個GUID產生器
都是英文不要怕，有個Generate UUID/GUID的按鈕按下去
下面會出現一行像是亂碼的
那個就是GUID囉，如果還是無法成功新增值，就多產生幾次吧
國父都革命11次了我還沒有產生這麼多次都失敗的
如果真的一直無法新增成功拜託讓我知道

如果是要叫程式自己寫，也是可以
請看繼續閱讀~

選取前n筆資料

是說PL/SQL要做到此功能需要用到 row_number 的功能，MS SQL則是使用TOP比較簡單(嗎?)
今天試用了一下發現怎麼叫都叫不出來

想到之前用teradata的經驗是，一定要取別名!!!
不取別名好像就不是個資料表一樣!!

PL/SQL就沒這麼龜毛，心想該不會MS SQL也在這種奇怪的地方有所堅持吧..

一試之下果然要取別名，這死孩子!