神奇的 SQL 之 CASE表达式,妙用多多 !

  • 时间:
  • 浏览:3
  • 来源:大发pk10_pk10IOS下载_大发pk10IOS下载

前言

  历史考试选泽题:黄花岗起义第一枪谁开的? A宋教仁 B孙中山 C黄兴 D徐锡麟,考生选C。

  又看第二题:黄花岗起义第二枪谁开的? 考生傻了,就选了个B。

  接着看第三题:黄花岗起义中,第三枪谁开的? 考生疯了,胡乱选了A。

  考试出来就去找出卷老师。老师拿下课本说:黄兴连开三枪,揭开了黄花岗起义的序幕。考生:......

CASE表达式 之概念

  相信朋友都用过CASE表达式,尤其是做一点统计功能的过后,用的有点硬多,可真要说一点是 CASE表达式,我估计还真没几买车人能清楚的表述出来。CASE表达式和 “2+1” 过后 “120/3” 从前的表达式一样,是有一种进行运算的功能,正如CASE(具体情况)一点词的含义一样,用于区分具体情况,在有条件分歧的过后使用它。CASE表达式是从 SQL-92 标准过后始于被引入的,过后过后它是相对较新的技术,所以尽管使用起来非常便利,但其真正的价值却无须为啥为人所知。所以人无需它,过后用它的简略版函数,例如 DECODE(Oracle)、IF(MySQL)等。然而,CASE表达式他说是 SQL-92 标准里加入的最有用的型态,以时需用好它,必须 SQL 能处理的问提就会更广泛,写法也会更加漂亮,因此,过后 CASE表达式 是不依赖于具体数据库的技术,所以时需提高 SQL 代码的可移植性。

  基本格式如下

-- 简单 CASE表达式
CASE 列(或表达式)
     WHEN <匹配值1> THEN <表达式>
     WHEN <匹配值2> THEN <表达式>
     ......
     ELSE <表达式>
END

-- 搜索 CASE表达式
CASE WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     WHEN <判断表达式> THEN <表达式>
     ......
     ELSE <表达式>
END


-- 简单 CASE表达式 示例
CASE sex
    WHEN '1' THEN ''
    WHEN '2' THEN ''
    ELSE '一点' 
END

-- 搜索CASE表达式 示例
CASE WHEN sex = '1' THEN ''
     WHEN sex = '2' THEN ''
     ELSE '一点' 
END

  CASE表达式 的 ELSE子句 时需省略,但推荐无须省略,省略了过后要出显朋友意料之外的结果。END必须省,时需有。当 WHEN子句 为真时,CASE表达式 的真假值判断就会中止,而剩余的 WHEN子句会被忽略。为了处理引起无时需的混乱,使用 WHEN子句 时需注意条件的排他性。

  简单CASE表达式正如其名,写法简单,但能实现的功能比较有限。简单CASE表达式能写的条件,搜索CASE表达式无需 写,所以基本上采用搜索CASE表达式的写法。

CASE表达式 之妙用

  顶端讲了 CASE表达式 的理论知识,感觉不痛不痒,必须接下来朋友进入实战篇,结合一点场景来看看 CASE表达式 的妙用

  行转列

    过后朋友用的更多的是 IF(MySQL)或 DECODE(Oracle),但这两者都全部后要标准的 SQL,更推荐朋友用 CASE表达式,移植性更高

    假设朋友有如下表,以及如下数据

CREATE TABLE t_customer_credit (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
    login_name VARCHAR(60

) NOT NULL COMMENT '登录名',
    credit_type TINYINT(1) NOT NULL COMMENT '额度类型,1:自由资金,2:冻结资金,3:优惠',
    amount DECIMAL(22,6) NOT NULL DEFAULT '0.00000' COMMENT '额度值',
    create_by VARCHAR(60

) NOT NULL COMMENT '创建者',
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
    update_by VARCHAR(60

) NOT NULL COMMENT '修改者',
  PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 560

.000000, 'system', '2019-7-7 11:60

:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:60

:09', '2019-7-7 11:60

:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:60

:09', '2019-7-7 11:60

:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:60

:09', '2019-7-7 11:60

:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:60

:09', '2019-7-7 11:60

:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:60

:09', '2019-7-7 11:60

:09', 'system');
View Code

    过后朋友要一行显示用户的另另三个白额度,而全部后要 3 条记录显示 3 个额度,朋友应该为啥做,法律法律依据有所以种,这里提供如下 3 种

-- 1、最容易想到的IF,不具备移植性,不推荐
SELECT login_name,
    MAX(IF(credit_type=1, amount, 0)) freeAmount,
    MAX(IF(credit_type=2, amount, 0)) freezeAmount,
    MAX(IF(credit_type=3, amount, 0)) promotionAmount
FROM t_customer_credit GROUP BY login_name;

-- 2、CASE表达式,标准的 SQL 规范,具备移植性,推荐使用
SELECT login_name,
    MAX(CASE WHEN credit_type = 1 THEN amount ELSE 0 END) freeAmount,
    MAX(CASE WHEN credit_type = 2 THEN amount ELSE 0 END) freezeAmount,
    MAX(CASE WHEN credit_type = 3 THEN amount ELSE 0 END) promotionAmount
FROM t_customer_credit GROUP BY login_name;

-- 3、自连接,数据量大的具体情况下,结合索引,下行带宽

不错,具备移植性
SELECT
    a.login_name,a.amount freeAmount,
    b.amount freezeAmount,
    c.amount promotionAmount
FROM (
    SELECT login_name, amount FROM t_customer_credit WHERE credit_type = 1
)a
LEFT JOIN t_customer_credit b ON a.login_name = b.login_name AND b.credit_type = 2
LEFT JOIN t_customer_credit c ON a.login_name = c.login_name AND c.credit_type = 3;
View Code

    无论是 IF 还是 CASE表达式,都结合了 GROUP BY 与聚合函数,下行带宽 是个问提,而自连接是下行带宽 最高的,不管在都没人 login_name 加在索引

  转换统计

    将已有编号法律法律依据转换为新的法律法律依据并统计,在进行非定制化统计时,朋友老要会遇到将已有编号法律法律依据转换为另外有一种便于分析的法律法律依据并进行统计的需求。假设朋友有如下表

DROP TABLE t_province_population;
CREATE TABLE t_province_population (
  id tinyint(2) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  province_name varchar(60

) NOT NULL COMMENT '省份名',
  sex tinyint(1) NOT NULL COMMENT '性别,1:男,2:女',
  population int(11) NOT NULL COMMENT '人口数',
  PRIMARY KEY (id)
);

INSERT INTO t_province_population(province_name,sex,population)
VALUES
("黑龙江", 1 ,20),
("黑龙江", 2 ,18),
("内蒙古", 1 ,7),
("内蒙古", 2 ,8),
("海南", 1 ,20),
("海南", 2 ,22),
("西藏", 1 ,8),
("西藏", 2 ,7),
("浙江", 1 ,35),
("浙江", 2 ,35),
("台湾", 1 ,26),
("台湾", 2 ,23),
("河南", 1 ,40),
("河南", 2 ,38),
("湖北", 1 ,27),
("湖北", 2 ,24);

SELECT * FROM t_province_population;
View Code

    朋友时需按各个省所在的位置,统计出东南西北中,各个区域内的人口数量

      东:浙江、台湾,西:西藏,南:海南,北:黑龙江、内蒙古,中:湖北、河南

    过后村里人 随便说说一点表设计的不合理,应该在设计之初就应该多加另另三个白区域字段(district)来标明各省所属区域。最好的做法随便说说是从前,但这得时需朋友在设计之初的以时需考虑得到,过后有从前的需求,假设朋友设计之初必须从前的需求,而朋友也没考虑到,必须有必须一点法律法律依据来实现了? 朋友时需从前来写 SQL

    结果如下

    假设朋友时需对各个省份做另2买车人口数级别的统计,统计出各个级别的数量

      level_1:population < 20,level_2:20 <= population < 60 ,level_3:60 <= population < 70 ,level_4:>= 70;统计出 level_1 ~ level_4 的数量各有多少

    SQL 与执行结果如下

    一点转换统计还是比较常用的,重点过后 GROUP BY 子句的写法。

  条件分支

    SELECT 条件分支

      还是以顶端的 t_province_population 为例,过后朋友无需直观的知道各个省份的男、女数量具体情况,例如如下

      朋友要为啥写 SQL? 有如下有一种法律法律依据

      随便说说过后行转列,行转列更容易懂

    UPDATE 条件分支

      朋友有一张薪资表,如下

CREATE TABLE t_user_salaries(
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name varchar(60

) NOT NULL COMMENT '姓名',
    sex tinyint(1) NOT NULL COMMENT '性别,1:男,2:女',
  salary int(11) NOT NULL COMMENT '薪资',
  PRIMARY KEY (id)
);

INSERT INTO t_user_salaries(name, sex,salary) VALUES
("张三", 1, 60

00),
("李四", 1, 27000),
("王五", 1, 260

0),
("菲菲", 2, 260

0),
("赵六", 1, 29000);

SELECT * FROM t_user_salaries;
View Code

      假设现在时需根据以下条件对该表的数据进行更新:1、对当前工资为 60 00 元以上的员工,降薪 10%,2、对当前工资为 260 00 元以上且不满 260 00 元的员工,加薪 20%。调整过后的薪资如下所示

      乍一看,分别执行下面另另三个白 UPDATE 操作好像就时需做到,因此朋友执行下看看结果

      朋友发现张三的薪资不降反升了! 这是过后执行 条件1的SQL后,张三的薪资又满足条件2了,所以又更新了一遍,愿因他的薪资变多了,村里人 过后要说,把条件1和条件2的SQL换下顺序不就好几时,朋友来试试

      张三的薪资是降对了,可李四的薪资却涨错了!这是过后李四的薪资满足条件2,升了 20% 过后又满足条件1,又降了 10%。难道就必须就必须正确的法律法律依据了? 朋友来看看一点 SQL

      完美不? 有点硬完美,一点技巧的应用范围很广,值得朋友掌握

  CHECK 约束

    注意:CHECK 是标准的 SQL,因此 MySQL 却必须实现它,所以 CHECK 在 MySQL 中是不起作用的!

    回到朋友的薪资表,假设某个公司有从前另另三个白无理的规定:女人不员工的工资不得高于60 000,朋友过后实现它? 法律法律依据有有一种:1、代码层面控制 、2、数据库表加约束。

    代码层面控制就没人多说了,这朋友平时最能想到的,实际也是用的最多的;那从表约束,朋友该咋样实现了,像从前吗?

    必须实现让人发现公司的男同事后要提着刀来找你了,过后必须朋友的薪资,一点约束会愿因录入不了男性的薪资! 过后朋友的约束是:sex=2 AND salary < = 60 000 表示 “是女人不,因此薪资必须高于60 000”,而全部后要:“过后是女人不,薪资不高于60 000”。正确的约束条件应该必须写

  CASE表达式还有所以一点的用处,强大的不得了,因此深度1灵活;用好它,能村里人 歌词 写出更加契合的 SQL。

总结

  1、CASE表达式 是支撑 SQL 声明式编程的根基之一,也是灵活运用 SQL 时不可或缺的基础技能。作为表达式,CASE 表达式在执行后要被判定为另另三个白固定值,因此它时需写在聚合函数结构;也正过后它是表达式,所以时需写在SELECE 子句、GROUP BY 子句、WHERE 子句、ORDER BY 子句里。简单点说,在能写列名和常量的地方,通常时需写 CASE 表达式

  2、写 CASE表达式 的注意点

    a、各个分支返回的数据类型要一致

    b、养成写 ELSE 的好习惯

    c、无须忘了写 END

  3、多条件时,用 OR 、AND 等谓词,IF 函数也一样

参考

  《SQL基础教程》

  《SQL进阶教程》

猜你喜欢

三洋 WT8655YM0S 8公斤超音波大容量全自动波轮洗衣机 全模糊智能控制 二步净洗涤(亮灰色)好不好,优缺点,是否值得买

2018-08-0713:19:32醒***悟5分东西收到了,用了哪几次才来评价,容量大,动力足,洗衣干净。2018-08-0608:33:23j***a5分不错不错后后购买三

2020-01-19

止暴制乱\林郑:首务止暴制乱救经济

图:今年头九个月,出入口较去年同期分别录得4.6%和6.5%跌幅特区政府今日回应第三季经济数据,本港经济步入技术性衰退几成定局。行政长官林郑月娥昨日出席“亚洲金融贸易未来”研讨

2020-01-19

亚摩勒比塔VS雷奥亚免费视频直播,亚摩勒比塔VS雷奥亚比赛集锦,亚摩勒比塔VS雷奥亚录像,亚摩勒比塔VS雷奥亚首发阵容

首页新闻视频直播数据APP懂球号直播君广告合作方式亚摩勒比塔雷奥亚直播君|分析|集锦近期比赛萨索洛意甲2-1都灵纽卡斯尔联英超1-0切尔西奥萨苏纳西甲0-0巴拉多利德那不勒斯意

2020-01-19

触宝今晚赴美上市,董事长张瞰发公开信:AI是未来方向

9月28日下午消息,北京时间今晚10点,全球移动互联网公司、中国出海企业触宝将正式在美国纽交所挂牌上市,股票代码:CTK。触宝此次全球发售4315万股美国存托股票(ADS),每

2020-01-19

港版联想Moto Z迎来安卓7.1.1升级

IT之家7月19日消息,目前香港和印度尼西亚的MotoZ用户可能收到Android7.1.1更新,该机去年预装Android6.0上市,之后 更新到了Android7.0。用

2020-01-19