case语句在SQL中的使用方法大全

  

  

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语句,如下所示

–条件1
UPDATE Personnel
SET salary = salary * 0.9
WHERE salary >= 5000;
–条件2
UPDATE Personnel
SET salary = salary * 1.15
WHERE salary >= 2000 AND salary < 4600;
可是工作没有想象得那么粗略,即使有个人工资5000块。首先,依照规则1,工资缩小10%,变成工资4500。接下来运行第二个SQL时候,因为这个人的工资是4500在2000到4600的限量之内,
需扩展15%,最终这个人的工薪结果是5175,不但没有滑坡,反而有增无减了。如若假定扭动执行,那么工资4600的人相反会变成缩短工资。暂且不论这么些条例是何等荒诞,倘使想要一个SQL
语句实现那个意义的话,大家需要用到Case函数。代码如下:

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语句举办询问。
条件1

–条件1:只选拔了一门科目的学员
SELECT std_id, MAX(class_id) AS main_class
FROM Studentclass
GROUP BY std_id
HAVING COUNT(*) = 1;
履行结果1

STD_ID   MAIN_class
——   ———-
300      4
400      5
500      6
条件2

–条件2:采用多门科目标学生
SELECT std_id, class_id AS main_class
FROM Studentclass
WHERE main_class_flg = ‘Y’ ;
实施结果2

STD_ID  MAIN_class
——  ———-
betway必威体育app官网,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。

 

Post Author: admin

发表评论

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