大发uu快3_uu快3棋牌_大发uu快3棋牌

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

时间:2020-01-11 21:24:28 出处:大发uu快3_uu快3棋牌_大发uu快3棋牌

前言

  历史考试确定题:黄花岗起义第一枪谁开的? 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(3000) 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(3000) 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(3000) NOT NULL COMMENT '修改者',
  PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 53000.000000, 'system', '2019-7-7 11:300:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:300:09', '2019-7-7 11:300:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:300:09', '2019-7-7 11:300:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:300:09', '2019-7-7 11:300:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:300:09', '2019-7-7 11:300:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:300:09', '2019-7-7 11:300: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(3000) 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 < 3000 ,level_3:3000 <= 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(3000) 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, 300000),
("李四", 1, 27000),
("王五", 1, 230000),
("菲菲", 2, 23000),
("赵六", 1, 29000);

SELECT * FROM t_user_salaries;
View Code

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

      乍一看,分别执行下面另有一个 UPDATE 操作好像就后要 能做到,假如亲戚亲戚大伙执行下看看结果

      亲戚亲戚大伙发现张三的薪资不降反升了! 这是可能执行 条件1的SQL后,张三的薪资又满足条件2了,这名 这名 又更新了一遍,原因分析分析 他的薪资变多了,一帮人可能会说,把条件1和条件2的SQL换下顺序不就好什么时间,亲戚亲戚大伙来试试

      张三的薪资是降对了,可李四的薪资却涨错了!这是可能李四的薪资满足条件2,升了 20% 如果又满足条件1,又降了 10%。难道就如此就如此正确的法子了? 亲戚亲戚大伙来看看这名 SQL

      完美不? 有点儿完美,这名 技巧的应用范围很广,值得亲戚亲戚大伙掌握

  CHECK 约束

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

    回到亲戚亲戚大伙的薪资表,假设某个公司有如此 另有一个无理的规定:男人的女人的女人员工的工资不得高于3000000,亲戚亲戚大伙可能实现它? 法子有四种 :1、代码层面控制 、2、数据库表加约束。

    代码层面控制就不要 说了,这亲戚亲戚大伙平时最能想到的,实际也是用的最多的;那从表约束,亲戚亲戚大伙该如何实现了,像如此 吗?

    如此实现假如你发现公司的男同事需用提着刀来找你了,可能如此亲戚大伙的薪资,这名 约束会原因分析分析 录入不了男性的薪资! 可能亲戚亲戚大伙的约束是:sex=2 AND salary < = 3000000 表示 “是男人的女人的女人,假如薪资只能高于3000000”,而需用:“可能是男人的女人的女人,薪资不高于3000000”。正确的约束条件应该如此写

  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进阶教程》

热门

热门标签