你的位置:首页 > 数据库

[数据库]小说一下case ~


case 这个关键词,用的地方不少~大部分的用途都通过以下的方式去应用

DECLARE @i INT = 3SELECT CASE @i     WHEN 1 THEN 1     WHEN 2 THEN 2     WHEN 3 THEN 3    END AS 测试1;测试1-----------3DECLARE @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.0003      秘书处                        Mery                        2012-03-17 00:00:00.0006      开发部                        Alex                        2012-03-03 00:00:00.0002      开发部                        John                        2013-04-23 00:00:00.0004      人事部                        Anna                        2014-05-07 00:00:00.0001      人事部                        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 INTDECLARE @T AS TABLE (I INT)WHILE @i < 100BEGIN  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 II      ----------- -----------NULL    270      341      242      14

 

( Null???Null怎么会出来的?? rand()*10 % 3 只有 (0,1,2) 3种情况啊!!你挺萌的在逗我?)

其实是这个样纸的,看一下语句,case 里面,没匹配一项,都需要将case when 里面的条件判断一次,所以每次都会执行一次 Rand()*10 这个表达式,导致可以非 1,2,3 的值粗线!如果要稳定,那就改成这样

DECLARE @i INT = 1,    @j INTDECLARE @T AS TABLE (I INT)WHILE @i < 100BEGIN  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 II      ----------- -----------0      401      242      35

看!这就没有了Null 了~

 

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