SQL中的CASE WHEN用法ITeye - AG环亚娱乐

SQL中的CASE WHEN用法ITeye

2019年03月25日14时19分11秒 | 作者: 辰韦 | 标签: 函数,运用,句子 | 浏览: 1815

中的CASE WHEN句子是常常要用到的,下面将结合实例,为您详解CASE WHEN句子的运用,供您参阅,期望对您学习SQL句子能有所协助。

一张表数据如下
1900-1-1 胜
1900-1-1 胜
1900-1-1 负
1900-1-2 胜
1900-1-2 胜
写出一条SQL句子,使检索成果如下:
          胜  负
1900-1-1 2   1
1900-1-2 2   0

我顺手建了这样一个表:

create table test(Date varchar(50) null, Result varchar(50) null)

 

并将上面的数据都刺进到表中。

经过一番测验和修正,总算得到了答案:

select distinct Date,
sum(case Result when 胜 then 1 else 0 end) as 胜,
sum(case Result when 负 then 1 else 0 end) as 负
from test
group by date

 

这儿我要说的,其实是SQL中case when的用法。它在一般的SQL句子中好像并不常见,我自己曾经也没在实践项目中运用过。遇到相似问题,往往经过代码或多条SQL句子完结。或者是如下这种丑恶的SQL,并且还伴随着许多潜在的BUG(如,当没有‘负’时)。

select a.date,a.a1 胜,b.b1 负 from 
(select date,count(date) a1 from test where Result =胜 group by date) a, 
(select date,count(date) b1 from test where Result =负 group by date) b 
where a.date=b.date

 补白:case when常用在需求对分组成果进行多项计算,如本例中按日期分组后,对胜和负进行计算;一般以where过滤条件来计算,只能计算一项目标,假如要计算多项目标,就需求多个子查询。

咱们无妨来温习一下CASE WHEN的语法。

CASE WHEN有两种用法,一种是相似上面比方中那样的简略CASE函数:

CASE result
 WHEN 胜 THEN 1
 WHEN 负 THEN 2
ELSE 0
END

 

还有一种是CASE查找函数:

CASE WHEN result=胜 THEN 1
 WHEN result=负 THEN 2
ELSE 0
END

 

其间result=胜能够替换为其他条件表达式。假如有多个CASE WHEN表达式契合条件,将只回来第一个契合条件的子句,其他子句将被疏忽。

用CASE WHEN句子能够简化咱们平常工作中遇到的许多问题。如性别在表中存的是数字1、2,可是期望查询出来男、女时,能够这样:

select (case Gender when 1 then 男 when 2 then 女 else 其他 end) as Gender from Ta
SQL中Case的运用办法       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,这个公司有个规则,女职工的薪酬有必要高于块。假如用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。      例,有如下更新条件      薪酬以上的职工,薪酬削减%       薪酬在到之间的职工,薪酬添加%       很简略考虑的是挑选履行两次UPDATE句子,如下所示      条件      UPDATE Personnel      SET salary = salary * 0.9      WHERE salary = 5000;      条件      UPDATE Personnel      SET salary = salary * 1.15      WHERE salary = 2000 AND salary 4600;      可是工作没有幻想得那么简略,假设有个人薪酬块。首要,依照条件,薪酬削减%,变成薪酬。接下来运转第二个SQL时分,由于这个人的薪酬是在到的规模之内,需添加%,最终这个人的薪酬成果是,不光没有削减,反而添加了。假如要是反过来履行,那么薪酬的人相反会变成削减薪酬。暂时不论这个规章是多么荒谬,假如想要一个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句子进行查询。      条件      条件:只挑选了一门课程的学生      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。         有一张表table1 结构如下.   table1:   username    action        A                   CALL          A                   SEARCH   A                   SEARCH   B                   CALL         B                   SEARCH    A                   SEARCH   A                   VIDEO   B                   SEARCH   B                   VIDEO   A                   CALL     B                   VIDEO     经过一条SQL句子得出下列计算数据:   table2   username   CALL_TIMES       SEARCH_TIMES      VIDEO_TIMES   -   A                     2                        3                              1   B                     1                        2                              2   -   阐明:   table.action 中所有或许的值都已知的,只要 CALL , SEARCH ,VIDEO 和 table2 的字段一一对应   MYSQL 句子:   BR select   BR      t.username,   BR      max(CASE T.action WHEN CALL THEN T.NN ELSE 0 end) as CALL_TIMES,   BR      max(CASE T.action WHEN SEARCH THEN T.NN ELSE 0 end) as SEARCH_TIMES,   BR      max(CASE T.action WHEN VIDEO THEN T.NN ELSE 0 end) as VIDEO_TIMES   BR   BR from   BR  (select temp.username,temp.action,count(*) as NN   BR   from temp   BR   group by temp.username,temp.action) as T   BR   BR group by T.username   BR   BR   BR   BR BR   对应的  句子:   select username,   BR      max(decode(action,CALL,NN,0)) AS CALL_TIMES ,   BR      max(decode(action,VIDEO,NN,0)) AS VIDEO_TIMES ,   BR      max(decode(action,SEARCH,NN,0)) AS VIDEO_TIMES   BR from   BR      (select username,action,count(*) AS NN from table1   BR      group by username,action)   BR         BR group by username   BR
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表AG环亚娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章

阅读排行

  • 1

    SQL中的CASE WHEN用法ITeye

    函数,运用,句子
  • 2
  • 3

    Oracle 11g 新特性ITeye

    毛病,指令,修正
  • 4
  • 5

    Oracle发送邮件ITeye

    邮件,发送,服务器
  • 6

    根本存储进程ITeye

    存储,进程,判别
  • 7

    NOSQL之旅ITeye

    咱们,能够,数据库
  • 8
  • 9
  • 10

    pro*c 动态sql二ITeye

    数据,过错,成功