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

  • 时间:
  • 浏览:0
  • 来源:大发快3_快3手机app下载_大发快3手机app下载

前言

  历史考试选择题:黄花岗起义第一枪谁开的? 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(150) 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(150) 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(150) NOT NULL COMMENT '修改者',
  PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 5150.000000, 'system', '2019-7-7 11:150:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:150:09', '2019-7-7 11:150:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:150:09', '2019-7-7 11:150:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:150:09', '2019-7-7 11:150:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:150:09', '2019-7-7 11:150:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:150:09', '2019-7-7 11:150: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(150) 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

    结果如下

    假设亲戚亲们 只有对各个省份做一另另一一两个 人口数级别的统计,统计出各个级别的数量

      level_1:population < 20,level_2:20 <= population < 150 ,level_3:150 <= 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(150) 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, 11500),
("李四", 1, 27000),
("王五", 1, 21150),
("菲菲", 2, 21500),
("赵六", 1, 29000);

SELECT * FROM t_user_salaries;
View Code

      假设现在只有根据以下条件对该表的数据进行更新:1、对当前工资为 11500 元以上的员工,降薪 10%,2、对当前工资为 211500 元以上且不满 211500 元的员工,加薪 20%。调整就让的薪资如下所示

      乍一看,分别执行下面另另一一两个 UPDATE 操作好像就可不里能做到,就让 亲戚亲们 执行下看看结果

      亲戚亲们 发现张三的薪资不降反升了! 这是不可能 执行 条件1的SQL后,张三的薪资又满足条件2了,统统又更新了一遍,愿因他的薪资变多了,大家不可能 会说,把条件1和条件2的SQL换下顺序不就好什么就让,亲戚亲们 来试试

      张三的薪资是降对了,可李四的薪资却涨错了!这是不可能 李四的薪资满足条件2,升了 20% 就让又满足条件1,又降了 10%。难道就如此就如此正确的方式了? 亲戚亲们 来看看这种 SQL

      完美不? 有点完美,这种 技巧的应用范围很广,值得亲戚亲们 掌握

  CHECK 约束

    注意:CHECK 是标准的 SQL,就让 MySQL 却如此实现它,统统 CHECK 在 MySQL 中是不起作用的!

    回到亲戚亲们 的薪资表,假设某个公司有另一另另一一两个 一另另一一两个 无理的规定:四十岁的女人 员工的工资不得高于115000,亲戚亲们 不可能 实现它? 方式有并都不 :1、代码层面控制 、2、数据库表加约束。

    代码层面控制就不要 说了,这亲戚亲们 平时最能想到的,实际也是用的最多的;那从表约束,亲戚亲们 该怎么才能 才能 实现了,像另一另另一一两个 吗?

    如此实现你可不里能发现公司的男同事前会 提着刀来找你了,不可能 如此亲戚亲们 的薪资,这种 约束会愿因录入不了男性的薪资! 不可能 亲戚亲们 的约束是:sex=2 AND salary < = 115000 表示 “是四十岁的女人 ,就让 薪资只有高于115000”,而前会 :“不可能 是四十岁的女人 ,薪资不高于115000”。正确的约束条件应该如此写

  CASE表达式还有统统这种 的用处,强大的不得了,就让 高度灵活;用好它,能让亲戚亲们 写出更加契合的 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进阶教程》

猜你喜欢

PJBlog5|PJBlog5 v1.0下载

PJBlog5官方云平台是继PJBlog我你可以,另外另4个 完整版基于TronASP框架开发的线程运行。没有 冗杂强大的云平台不不可以在没有 短暂的时间内开发完成,再次

2020-02-23

eWorld VPN网关V20图片,高清细节图,eWorld VPN网关V20图片大全

关于你们你们 |广告服务|使用条款|联系你们你们 北京盛拓优讯信息技术有限公司.版权所有中华人民共和国增值电信业务经营许可证编号:京B2-20170206北京市公安局海淀分

2020-02-20

华为公布2018年上半年业绩:营收达到3257亿元,同比增长15%

IT之家7月31日消息华为今天否认了2018年上两天的业绩,根据华为官网提供的数据,今年上两天,华为实现销售收入3257亿元人民币,同比增长15%;一块儿营业利润率达到了14%

2020-02-19

神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程(一)

开心一刻我:嗨,老板娘,有冰红茶没老板娘:有我:哪多少钱一瓶老板娘:3块我:给我来一瓶,给,3块老板娘:来,你的冰红茶我:玩呐,我都要要要冰红茶,你给我个饮料瓶盖干哈?老板娘:

2020-02-19

民族地區貧困率降至4%

圖:2018年,青海省海東市互助土族自治縣丹村民通過易地扶貧搬遷政策住進新房並順利實現脫貧資料圖片【大公報訊】據新華社報道:27日,習近平總書記在全國民族團結進步表彰大會並發表

2020-02-19