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
——  ———-
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。

本文来源CSDN博客,转发请标明出处:http://blog.csdn.net/huiquan346950757/archive/2009/11/26/4875634.aspx

发表评论

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

网站地图xml地图