小说一下case ~

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 了~

 

然后~这就说完了╮(╯_╰)╭