case 這個關鍵詞,用的地方不少~大部分的用途都通過以下的方式去應用 DECLARE @i INT = 3 SELECT CASE @i WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END AS 測試1; 測試1 ----------- 3 DECLARE
case 這個關鍵詞,用的地方不少~大部分的用途都通過以下的方式去應用
DECLARE @i INT = 3 SELECT CASE @i WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 END AS 測試1; 測試1 ----------- 3 DECLARE @Hour INT = DATEPART(hh,GETDATE()) SELECT CASE WHEN @Hour BETWEEN 7 AND 12 THEN '早上好' WHEN @Hour >= 12 AND @Hour < 18 THEN '下午好' WHEN @Hour > 18 AND @Hour < 24 THEN '晚上好' ELSE '深夜了' END AS 問候 問候 ------ 早上好
這個已經用得爐火純青╮(╯_╰)╭的小伙伴請不要鄙視我。
補充說一下,第一個查詢和第二個查詢的區別是第二個查詢有一個else ,這個else 是用來處理當都匹配不上的值。如果沒有else 的處理,如第一個例子的@i = 4 的時候,表達式將返回 Null。
再給一個例子,簡單創建一個員工表,然後通過使用case 可以做一些自定義的規則,這裡是按部門,優先是秘書處,然後開發部,然後人事部,最後其它部門不分先後,再根據入職時間來一個排序。
CREATE TABLE #Employee (ID INT IDENTITY(1,1),DeparmentName NVARCHAR(50),Name NVARCHAR(50),EntryDate DATETIME) INSERT INTO #Employee ( DeparmentName, Name, EntryDate ) VALUES ( N'人事部',N'Joey', '2015-04-12'), ( N'開發部',N'John', '2013-04-23'), ( N'秘書處',N'Mery', '2012-03-17'), ( N'人事部',N'Anna', '2014-05-07'), ( N'秘書處',N'Dave', '2011-01-12'), ( N'開發部',N'Alex', '2012-03-03') SELECT * FROM #Employee ORDER BY CASE DeparmentName WHEN '秘書處' THEN 1 WHEN '開發部' THEN 2 WHEN '人事部' THEN 3 ELSE 4 END,EntryDate ID DeparmentName Name EntryDate ----------- -------------------------------------------------- -------------------------------------------------- ----------------------- 5 秘書處 Dave 2011-01-12 00:00:00.000 3 秘書處 Mery 2012-03-17 00:00:00.000 6 開發部 Alex 2012-03-03 00:00:00.000 2 開發部 John 2013-04-23 00:00:00.000 4 人事部 Anna 2014-05-07 00:00:00.000 1 人事部 Joey 2015-04-12 00:00:00.000
使用case 還是要註意一些語法上面的使用,首先是聯機文檔裡面提到的2個
1 case 的嵌套上限是10
2 以下這個慄子是會執行報0除的錯誤的。因為 1/value 存在0,所以要註意執行的時候是需要註意表達式的值,不應依賴聚合後的值和when 的順序,這將會出現意外的結果。
WITH Data (value) AS ( SELECT 0 UNION ALL SELECT 1 ) SELECT CASE WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1 END FROM Data ;
3 註意then 後面的表達式,比如我將前面的查詢語句修改一下。因為then 後面的數據類型隱式轉換失敗,就會報下麵的錯誤,所以這個要小心
SELECT * FROM #Employee ORDER BY CASE DeparmentName WHEN '秘書處' THEN 1 WHEN '開發部' THEN 'a' --修改為'a' WHEN '人事部' THEN 3 ELSE 4 END,EntryDate 消息 245,級別 16,狀態 1,第 12 行 在將 varchar 值 'a' 轉換成數據類型 int 時失敗。
4 不光是 then 後面的 ,when 後面的值也需要註意數據類型的一致性。上述的例子如果將 開發部改成 1(不是 '1'),錯誤也成立 。
5 還有一種特殊的情況,在case 裡面使用函數,每次都會重新調用一次。比如以下一個例子
DECLARE @i INT = 1, @j INT DECLARE @T AS TABLE (I INT) WHILE @i < 100 BEGIN SELECT @j = CASE WHEN CAST(RAND()*10 AS INT)%3 = 0 THEN 0 WHEN CAST(RAND()*10 AS INT)%3 = 1 THEN 1 WHEN CAST(RAND()*10 AS INT)%3 = 2 THEN 2 END, @i = @i + 1 INSERT INTO @T ( I ) VALUES ( @j ) END SELECT I,COUNT(*) FROM @T GROUP BY I I ----------- ----------- NULL 27 0 34 1 24 2 14
( Null???Null怎麼會出來的?? rand()*10 % 3 只有 (0,1,2) 3種情況啊!!你挺萌的在逗我?)
其實是這個樣紙的,看一下語句,case 裡面,沒匹配一項,都需要將case when 裡面的條件判斷一次,所以每次都會執行一次 Rand()*10 這個表達式,導致可以非 1,2,3 的值粗線!如果要穩定,那就改成這樣
DECLARE @i INT = 1, @j INT DECLARE @T AS TABLE (I INT) WHILE @i < 100 BEGIN SELECT @j = CAST(RAND()*10 AS INT) , @j = CASE @j%3 WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 END, @i = @i + 1 INSERT INTO @T ( I ) VALUES ( @j ) END SELECT I,COUNT(*) FROM @T GROUP BY I I ----------- ----------- 0 40 1 24 2 35
看!這就沒有了Null 了~
然後~這就說完了╮(╯_╰)╭