sql server中case when的用法

Case具有三种格式。不难Case函数和Case搜索函数。

–简单Case函数

CASE sex

         WHEN ‘1’ THEN ‘男’

         WHEN ‘2’ THEN ‘女’

ELSE ‘其他’ END

–Case搜索函数

CASE WHEN sex = ‘1’ THEN ‘男’

         WHEN sex = ‘2’ THEN ‘女’

ELSE ‘其他’ END

 

 

那三种方法,可以完毕均等的功效。容易Case函数的写法相对相比较简单,可是和Case搜索函数相比较,成效方面会略带限制,比如写判断式。

还有一个急需专注的题材,Case函数只回去首个符合条件的值,剩下的Case部分将会被自动忽略。

 

–比如说,上边那段SQL,你永远不可能取得“第二类”那一个结果

CASE WHEN col_1 IN ( ‘a’, ‘b’) THEN ‘第一类’

         WHEN col_1 IN (‘a’)       THEN ‘第二类’

ELSE’其他’ END

 

上面大家来看一下,使用Case函数都能做些什么业务。

 

一,已知多少根据此外一种方法开展分组,分析。

 

有如下数据:(为了看得更明了,我并从未运用国家代码,而是平素用国家名作为Primary Key)

国家(country)人口(population)

中国600

美国100

加拿大100

英国200

法国300

日本250

德国200

墨西哥50

印度250

 

据悉那么些国家人口数量,统计南美洲和亚洲的人口数量。应该得到上边那一个结果。

洲人口

亚洲1100

北美洲250

其他700

 

想要解决这一个难题,你会怎么办?生成一个带有洲Code的View,是一个解决措施,可是这么很难动态的改变总结的方法。

比方运用Case函数,SQL代码如下:

SELECT  SUM(population),

        CASE country

                WHEN ‘中国’     THEN ‘亚洲’

                WHEN ‘印度’     THEN ‘亚洲’

                WHEN ‘日本’     THEN ‘亚洲’

                WHEN ‘美国’     THEN ‘北美洲’

                WHEN ‘加拿大’  THEN ‘北美洲’

                WHEN ‘墨西哥’  THEN ‘北美洲’

        ELSE ‘其他’ END

FROM    Table_A

GROUP BY CASE country

                WHEN ‘中国’     THEN ‘亚洲’

                WHEN ‘印度’     THEN ‘亚洲’

                WHEN ‘日本’     THEN ‘亚洲’

                WHEN ‘美国’     THEN ‘北美洲’

                WHEN ‘加拿大’  THEN ‘北美洲’

                WHEN ‘墨西哥’  THEN ‘北美洲’

        ELSE ‘其他’ END;

 

 

一致的,大家也得以用那个艺术来判断薪俸的阶段,并计算每一等级的人头。SQL代码如下;

 

SELECT

        CASE WHEN salary <= 500 THEN ‘1’

             WHEN salary > 500 AND salary <= 600  THEN ‘2’

             WHEN salary > 600 AND salary <= 800  THEN ‘3’

             WHEN salary > 800 AND salary <= 1000 THEN ‘4’

        ELSE NULL END salary_class,

        COUNT(*)

FROM    Table_A

GROUP BY

        CASE WHEN salary <= 500 THEN ‘1’

             WHEN salary > 500 AND salary <= 600  THEN ‘2’

             WHEN salary > 600 AND salary <= 800  THEN ‘3’

             WHEN salary > 800 AND salary <= 1000 THEN ‘4’

        ELSE NULL END;

 

 

二,用一个SQL语句达成不一样标准的分组。

 

有如下数据

国家(country)性别(sex)人口(population)

中国1 340

中国2 260

美国1 45

美国2 55

加拿大1 51

加拿大2 49

英国1 40

英国2 60

 

遵循国家和性别进行分组,得出结果如下

国家男女

中国340 260

美国45 55

加拿大51 49

英国40 60

 

日常情形下,用UNION也落到实处用一条语句举行询问。可是这样扩充消耗(八个Select部分),而且SQL语句会相比长。

上边是一个是用Case函数来成功这几个职能的例证

 

SELECT country,

       SUM( CASE WHEN sex = ‘1’ THEN

                      population ELSE 0 END),  –男性人口

       SUM( CASE WHEN sex = ‘2’ THEN

                      population ELSE 0 END)   –女性人口

FROM  Table_A

GROUP BY country;

 

 

那样我们应用Select,已毕对二维表的出口方式,充裕突显了Case函数的强劲。

 

三,在Check中使用Case函数。

 

在Check中使用Case函数在诸多气象下都是丰富正确的化解措施。可能有过几人平昔就无须Check,那么我提议您在看过上面的例子之后也尝试一下在SQL中行使Check。

上面大家来举个例证

供销社A,这些公司有个规定,女人士的薪金必须高于1000。假如用Check和Case来展现来说,如下所示

CONSTRAINT check_salary CHECK

           ( CASE WHEN sex = ‘2’

                  THEN CASE WHEN salary > 1000

                        THEN 1 ELSE 0 END

                  ELSE 1 END = 1 )

 

 

万一单独利用Check,如下所示

 

CONSTRAINT check_salary CHECK

           ( sex = ‘2’ AND salary > 1000 )

 

 

女人员的口径倒是符合了,男人士就不可以输入了。

 

 

 

四,依照条件有采纳的UPDATE。

 

例,有如下更新标准

工薪5000以上的职员,薪酬裁减10%

工薪在2000到4600里边的人员,薪给扩充15%

很不难考虑的是选拔执行一次UPDATE语句,如下所示

 

–条件

UPDATE Personnel

SET salary = salary * 0.9

WHERE salary >= 5000;

–条件

UPDATE Personnel

SET salary = salary * 1.15

WHERE salary >= 2000 AND salary < 4600;

 

唯独工作没有想像得那么粗略,假如有私房薪俸5000块。首先,根据原则,薪俸减少10%,变成薪给。接下来运行第一个SQL时候,因为这厮的工薪是在到的限定之内,需追加15%,最终这厮的报酬结果是,不但没有减弱,反而有增无减了。如果一旦扭动执行,那么薪俸的人相反会变成收缩报酬。暂且不论那些条例是多么荒诞,即便想要一个SQL
语句达成那一个职能的话,我们须求用到Case函数。代码如下:

CASE WHEN 搜索函數

UPDATE Personnel

SET salary = CASE WHEN salary >= 5000

            THEN salary * 0.9

WHEN salary >= 2000 AND salary < 4600

THEN salary * 1.15

ELSE salary END;

 

那里要留意一点,最终一行的ELSE
salary是不可或缺的,如果没有那行,不相符那七个标准化的人的薪资将会被写成NUll,那可就大事不妙了。在Case函数中Else部分的默许值是NULL,那点是索要小心的地点。

那种方式仍是可以在许多地方使用,比如说变更主键那种累活。

诚如景色下,要想把两条数据的Primary key,a和b调换,须要经过临时存储,拷贝,读回数据的多少个进度,要是使用Case函数的话,一切都变得不难多了。

p_key col_1 col_2

a 1 张三

b 2 李四

c 3 王五

 

 

假如有如上数据,必要把主键a和b相互互换。用Case函数来促成的话,代码如下

 

UPDATE SomeTable

SET p_key = CASE WHEN p_key = ‘a’

THEN ‘b’

WHEN p_key = ‘b’

THEN ‘a’

ELSE p_key END

WHERE p_key IN (‘a’, ‘b’);

 

一样的也可以互换五个Unique key。须要留意的是,假使有亟待交流主键的事态暴发,多半是当场对那些表的宏图开展得不够到位,提出检查表的安排是或不是妥善。

 

五,七个表数据是还是不是一律的自我批评。

 

Case函数不一致于DECODE函数。在Case函数中,可以运用BETWEEN,LIKE,IS NULL,IN,EXISTS等等。比如说使用IN,EXISTS,可以拓展子查询,从而完结越多的听从。

上面具个例证来验证,有多个表,tbl_A,tbl_B,多个表中都有keyCol列。现在大家对五个表举办相比较,tbl_A中的keyCol列的数额要是在tbl_B的keyCol列的数量中得以找到,重返结果’Matched’,若是没有找到,再次来到结果’Unmatched’。

要完毕上边这么些成效,可以运用上边两条语句

 

–使用IN的时候

SELECT keyCol,

CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B )

THEN ‘Matched’

ELSE ‘Unmatched’ END Label

FROM tbl_A;

–使用EXISTS的时候

SELECT keyCol,

CASE WHEN EXISTS ( SELECT * FROM tbl_B

WHERE tbl_A.keyCol = tbl_B.keyCol )

THEN ‘Matched’

ELSE ‘Unmatched’ END Label

FROM tbl_A;

 

应用IN和EXISTS的结果是同等的。也可以利用NOT IN和NOT EXISTS,不过这一个时候要专注NULL的景况。

 

六,在Case函数中使用协议函数

 

如果有上面一个表

学号(std_id) 课程ID(class_id) 课程名(class_name) 主修flag(main_class_flg)

100 1 经济学Y

100 2 历史学N

200 2 历史学N

200 3 考古学Y

200 4 计算机N

300 4 计算机N

400 5 化学N

500 6 数学N

 

局地学员选取了并且修几门学科(100,200)也有些学员只采取了一门课程(300,400,500)。选修多门学科的学生,要采纳一门课程作为主修,主修flag里面写入Y。只接纳一门科目的学习者,主修flag为N(实际上即使写入Y的话,就从不下边的麻烦事了,为了举例子,还请多多包括)。

前些天大家要依照下边五个规格对那几个表展开询问

只选修一门学科的人,重临那门科目标ID

选修多门课程的人,重回所选的主课程ID

 

简单的想法就是,执行两条不一致的SQL语句举办查询。

条件

 

–条件:只接纳了一门科目标学员

SELECT std_id, MAX(class_id) AS main_class

FROM Studentclass

GROUP BY std_id

HAVING COUNT(*) = 1;

 

推行结果

 

STD_ID   MAIN_class

——   ———-

300      4

400      5

500      6

 

条件

 

–条件:选取多门科目的学习者

SELECT std_id, class_id AS main_class

FROM Studentclass

WHERE main_class_flg = ‘Y’ ;

 

推行结果

 

STD_ID  MAIN_class

——  ———-

100     1

200     3

 

只要利用Case函数,大家假如一条SQL语句就足以化解难点,具体如下所示

 

SELECT  std_id,

CASE WHEN COUNT(*) = 1  –只拔取一门学科的学员的状态

THEN MAX(class_id)

ELSE MAX(CASE WHEN main_class_flg = ‘Y’

THEN class_id

ELSE NULL END

)

END AS main_class

FROM Studentclass

GROUP BY std_id;

 

运转结果

 

STD_ID   MAIN_class

——   ———-

100      1

200      3

300      4

400      5

500      6

 

经过在Case函数中嵌套Case函数,在商事函数中拔取Case函数等措施,大家能够轻松的缓解那些题材。使用Case函数给我们带来了更大的自由度。

最终提示一下选取Case函数的新手注意不要犯上边的谬误

 

CASE col_1

WHEN 1       THEN ‘Right’

WHEN NULL  THEN ‘Wrong’

END

 

在这一个讲话中When Null这一行总是重回unknown,所以永远不会并发Wrong的情事。因为那句可以轮换成WHEN col_1 = NULL,那是一个张冠李戴的用法,那些时候大家应该接纳用WHEN col_1 IS NULL。

行转列(case when用法)

数据库原来的数据:如下图

图片 1

修改后的数码:

select  ball as “球”,sum(case type when ‘红’ then num else 0 end) as ‘红’,

                     sum(case  type when ‘蓝’ then num else 0 end) as ‘蓝’,

                     sum(case  type when ‘绿’ then num else 0 end) as ‘绿’ 

                     from T_ball group by ball;

如下图

图片 2

 

 

 

 

范围分组查询(group by, case)

要求:查询在指定年龄段的食指

 

 

select case when age>1 and age <26  then 1
 when age>25 and age <27 then 2
              else 0 end as stage ,count(*) as population from
student 

group by case when age>1 and age <26  then 1
 when age>25 and age <27 then 2

              else 0 end

 

结果

        stage   population

1        1             2

2        2             5

 

可由此stage的值区分该条记录是属于哪个年龄段

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图