SQL 中的 CASE 表达式妙用,涨姿势了。。

这篇具有很好参考价值的文章主要介绍了SQL 中的 CASE 表达式妙用,涨姿势了。。。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

历史考试选择题:黄花岗起义第一枪谁开的? 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 代码的可移植性。

推荐一个开源免费的 Spring Boot 实战项目:

https://github.com/javastacks/spring-boot-best-practice

基本格式如下

-- 简单 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(50) 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(50) 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(50) NOT NULL COMMENT '修改者',
  PRIMARY KEY (id)
);
INSERT INTO `t_customer_credit` VALUES (1, 'zhangsan', 1, 550.000000, 'system', '2019-7-7 11:30:09', '2019-7-8 20:21:05', 'system');
INSERT INTO `t_customer_credit` VALUES (2, 'zhangsan', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (3, 'zhangsan', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (4, 'lisi', 1, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (5, 'lisi', 2, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');
INSERT INTO `t_customer_credit` VALUES (6, 'lisi', 3, 0.000000, 'system', '2019-7-7 11:30:09', '2019-7-7 11:30:09', 'system');

如果我们要一行显示用户的三个额度,而不是 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;

SQL 中的 CASE 表达式妙用,涨姿势了。。

无论是 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(50) 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;

SQL 中的 CASE 表达式妙用,涨姿势了。。

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

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

可能有人觉得这个表设计的不合理,应该在设计之初就应该多加一个区域字段(district)来标明各省所属区域。最好的做法确实是这样,但这得需要我们在设计之初的时候能考虑得到,或者有这样的需求,假设我们设计之初没有这样的需求,而我们也没考虑到,那么有没有什么办法来实现了?我们可以这样来写 SQL

-- 通用写法,适用于多种数据库
SELECT CASE province_name
    WHEN '浙江' THEN '东'
    WHEN '台湾' THEN '东'
    WHEN '海南' THEN '南'
    WHEN '西藏' THEN '西'
    WHEN '黑龙江' THEN '北'
    WHEN '内蒙古' THEN '北'
    WHEN '河南' THEN '中'
    WHEN '湖北' THEN '种'
    ELSE '其他' END district,
    SUM(population) populations
FROM t_province_population
GROUP BY CASE province_name
    WHEN '浙江' THEN '东'
    WHEN '台湾' THEN '东'
    WHEN '海南' THEN '南'
    WHEN '西藏' THEN '西'
    WHEN '黑龙江' THEN '北'
    WHEN '内蒙古' THEN '北'
    WHEN '河南' THEN '中'
    WHEN '湖北' THEN '中'
    ELSE '其他' END;

-- MySQL支持写法,移植性差
SELECT CASE province_name
    WHEN '浙江' THEN '东'
    WHEN '台湾' THEN '东'
    WHEN '海南' THEN '南'
    WHEN '西藏' THEN '西'
    WHEN '黑龙江' THEN '北'
    WHEN '内蒙古' THEN '北'
    WHEN '河南' THEN '中'
    WHEN '湖北' THEN '中'
    ELSE '其他' END district,
    SUM(population) populations
FROM t_province_population
GROUP BY district;

结果如下

SQL 中的 CASE 表达式妙用,涨姿势了。。

假设我们需要对各个省份做一个人口数级别的统计,统计出各个级别的数量

  • level_1:population < 20
  • level_2:20 <= population < 50
  • level_3:50 <= population < 70
  • level_4:>= 70

统计出 level_1 ~ level_4 的数量各有多少

SQL 与执行结果如下

SELECT
    CASE WHEN population < 20 THEN 'level_1'
        WHEN population >= 20 AND population < 50 THEN 'level_2'
        WHEN population >= 50 AND population < 70 THEN 'level_3'
        WHEN population >= 70 THEN 'level_4'
        ELSE NULL
    END pop_level,
    COUNT(*) cnt
FROM (
    SELECT province_name,SUM(population) population FROM t_province_population GROUP BY province_name
)a
GROUP BY
    CASE WHEN population < 20 THEN 'level_1'
        WHEN population >= 20 AND population < 50 THEN 'level_2'
        WHEN population >= 50 AND population < 70 THEN 'level_3'
        WHEN population >= 70 THEN 'level_4'
        ELSE NULL
    END;

SQL 中的 CASE 表达式妙用,涨姿势了。。

这种转换统计还是比较常用的,重点就是 GROUP BY 子句的写法。

条件分支

SELECT 条件分支

还是以上面的 t_province_population 为例,如果我们想要直观的知道各个省份的男、女数量情况,类似如下

SQL 中的 CASE 表达式妙用,涨姿势了。。

我们要怎么写 SQL?有如下两种方法

-- 1、CASE表达式 集合 GROUP BY
SELECT province_name,
    SUM(CASE WHEN sex = 1 THEN population ELSE 0 END) c,
    SUM(CASE WHEN sex = 2 THEN population ELSE 0 END) f_pops
FROM t_province_population
GROUP BY province_name;

-- 2、自关联
SELECT t.province_name, t.population m_pops, a.population f_pops
FROM t_province_population t
LEFT JOIN t_province_population a
ON t.province_name = a.province_name
WHERE t.sex = 1 AND a.sex = 2;

其实就是行转列,行转列更容易懂

UPDATE 条件分支

我们有一张薪资表,如下

CREATE TABLE t_user_salaries(
  id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  name varchar(50) 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, 30000),
("李四", 1, 27000),
("王五", 1, 22000),
("菲菲", 2, 24000),
("赵六", 1, 29000);

SELECT * FROM t_user_salaries;

SQL 中的 CASE 表达式妙用,涨姿势了。。

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

SQL 中的 CASE 表达式妙用,涨姿势了。。

乍一看,分别执行下面两个 UPDATE 操作好像就可以做到,但是我们执行下看看结果

-- 条件1
UPDATE t_user_salaries
SET salary = salary * 0.9
WHERE salary >= 30000;

-- 条件2
UPDATE t_user_salaries
SET salary = salary * 1.2
WHERE salary >= 25000 AND salary < 28000;

SQL 中的 CASE 表达式妙用,涨姿势了。。

我们发现张三的薪资不降反升了!这是因为执行 条件1的SQL后,张三的薪资又满足条件2了,所以又更新了一遍,导致他的薪资变多了,有人可能会说,把条件1和条件2的SQL换下顺序不就好了吗,我们来试试

-- 条件2
UPDATE t_user_salaries
SET salary = salary * 1.2
WHERE salary >= 25000 AND salary < 28000;

-- 条件1
UPDATE t_user_salaries
SET salary = salary * 0.9
WHERE salary >= 30000;

SQL 中的 CASE 表达式妙用,涨姿势了。。

张三的薪资是降对了,可李四的薪资却涨错了!这是因为李四的薪资满足条件2,升了 20% 之后又满足条件1,又降了 10%。难道就没有就没有正确的方式了?我们来看看这个 SQL

UPDATE t_user_salaries SET salary =
    CASE WHEN salary >= 30000 THEN salary * 0.9
            WHEN salary >= 25000 AND salary < 28000 THEN salary * 1.2
            ELSE salary
    END;

SELECT * FROM t_user_salaries;

SQL 中的 CASE 表达式妙用,涨姿势了。。

完美不?特别完美,这个技巧的应用范围很广,值得我们掌握

CHECK 约束

注意:CHECK 是标准的 SQL,但是 MySQL 却没有实现它,所以 CHECK 在 MySQL 中是不起作用的!

回到我们的薪资表,假设某个公司有这样一个无理的规定:女性员工的工资不得高于50000,我们如果实现它? 方式有两种:1、代码层面控制 、2、数据库表加约束。

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

-- 创建表的时候增加约束
CREATE TABLE t_user_salaries_check(
  name varchar(50) NOT NULL COMMENT '姓名',
    sex tinyint(1) NOT NULL COMMENT '性别,1:男,2:女',
  salary int(11) NOT NULL COMMENT '薪资',
    CONSTRAINT chk_sex_salary CHECK (sex=2 AND salary <= 50000)
);

-- 若t_user_salaries_check已创建,则补充上约束
ALTER TABLE t_user_salaries_check
ADD CONSTRAINT chk_sex_salary CHECK (sex=2 AND salary <= 50000);

这么实现你会发现公司的男同事都会提着刀来找你了,因为没有他们的薪资,这个约束会导致录入不了男性的薪资!因为我们的约束是:sex=2 AND salary < = 50000 表示 “是女性,并且薪资不能高于50000”,而不是:“如果是女性,薪资不高于50000”。正确的约束条件应该这么写

-- 创建表的时候增加约束
CREATE TABLE t_user_salaries_check(
  name varchar(50) NOT NULL COMMENT '姓名',
    sex tinyint(1) NOT NULL COMMENT '性别,1:男,2:女',
  salary int(11) NOT NULL COMMENT '薪资',
  PRIMARY KEY (id),
    CONSTRAINT chk_sex_salary CHECK(
        CASE WHEN sex = 2 THEN
                        CASE WHEN salary <= 50000 THEN 1
                                ELSE 0
                        END
                ELSE 1
        END = 1 )
);

-- 若t_user_salaries_check已创建,则补充上约束
ALTER TABLE t_user_salaries_check
ADD CONSTRAINT chk_sex_salary CHECK(
    CASE WHEN sex = 2 THEN
                        CASE WHEN salary <= 50000 THEN 1
                                ELSE 0
                        END
                ELSE 1
        END = 1
);

CASE表达式还有很多其他的用处,强大的不得了,而且高度灵活;用好它,能让我们写出更加契合的 SQL。

总结

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

2、写 CASE表达式 的注意点

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

b、养成写 ELSE 的好习惯

c、不要忘了写 END

来源:cnblogs.com/youzhibing/p/11240536.html

更多文章推荐:

1.Spring Boot 3.x 教程,太全了!

2.2,000+ 道 Java面试题及答案整理(2024最新版)

3.免费获取 IDEA 激活码的 7 种方式(2024最新版)

觉得不错,别忘了随手点赞+转发哦!文章来源地址https://www.toymoban.com/news/detail-777796.html

到了这里,关于SQL 中的 CASE 表达式妙用,涨姿势了。。的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Django笔记二十三之case、when操作条件表达式搜索、更新等操作

    本文首发于公众号:Hunter后端 原文链接:Django笔记二十三之条件表达式搜索、更新等操作 这一篇笔记将介绍条件表达式,就是如何在 model 的使用中根据不同的条件筛选数据返回。 这个操作类似于数据库中 if elif else 的逻辑。 以下是本篇笔记的目录: model 和数据准备 When 和

    2023年04月13日
    浏览(65)
  • 【SQL-正则】利用正则表达式进行过滤操作(常用正则表达式)

    1、由数字、26个英文字母或者下划线组成的字符串 2、非负整数(正整数 + 0 ) 3、正整数 4、非正整数(负整数 + 0) 5、负整数 6、整数 7、非负浮点数(正浮点数 + 0) 8、正浮点数 9、非正浮点数(负浮点数 + 0) 10、负浮点数 11、浮点数 12、由26个英文字母组成的字符串 13、

    2024年02月12日
    浏览(79)
  • SQL-正则表达式和约束

    正则表达式 约束 正则表达式是一种用来描述字符串模式的工具,它可以用于匹配、查找、替换等操作。正则表达式由字符和特殊字符组成,可以使用这些字符来定义匹配规则。 常用的正则表达式字符和特殊字符有: 普通字符:表示匹配该字符本身,如 a 表示匹配字母\\\"a\\\"。

    2024年02月08日
    浏览(45)
  • SQL中常见正则表达式用法

    在 SQL 中,正则表达式是一种强大的工具,用于匹配相应的字符串模式。SQL 支持的正则表达式语法因数据库而异,下面是一些常用的正则表达式元字符: . :匹配任何单个字符 * :匹配任意数量的前一个字符 + :匹配一个或多个前一个字符 ? :匹配零个或一个前一个字符 ^ :

    2024年02月15日
    浏览(63)
  • 【SQL篇】窗口函数和公共表达式

    思路 先给每个数进行排名 用这些数减去自己的排名,如果减了之后的结果是一样的,说明这几个数是连续的 用logid减去排名得出来的数进行group by,也就是把连续的数全都放在一个一个小组里面,求出每个小组的最大值和最小值就可以了 1596题 总结 这里有个坑,count并不会根

    2024年02月02日
    浏览(39)
  • 030、SQL语句之数据类型与表达式

    类型 存储空间 最小值(有符号/无符号) 最大值(有符号/无符号) TINYINT 1 -128 / 0 127 / 255 SMALLINT 2 -32768 / 0 32767 / 65535 MEDIUMINT 3 -8388608 / 0 8388607 / 16777215 INT 4 -2147483648 / 0 BIGINT 8 -9223372036854775808 / 0 9223372036854775807 / 18446744073709551615 注意unsigned: 不允许负数 用于精确数值:整数、小数或两

    2024年02月13日
    浏览(46)
  • 正则表达式中的“回引用(回溯)”——别名引用与序号引用的差异及正则表达式中的“P”关键字

    读到一段巧妙的正则表达式,勾起我对正则表达式欠缺知识点的探寻: P y t h o n Python P y t h o n 正则表达式中的“回引用 (回溯) ”——分组别名引用与序号引用的差异及正则表达式中的“P”详情。 (笔记模板由python脚本于2024年01月14日 07:49:35创建,本篇笔记适合 熟悉基

    2024年01月19日
    浏览(46)
  • Python中的赋值表达式

    赋值表达式(assignment expression)是Python 3.8新引入的语法,它会用到海象操作符(walrus operator)。 这种写法可以解决某些持续已久的代码重复问题。a = b是一条普通的赋值语句,读作a equals b,而a := b则是赋值表达式,读作a walrus b。 这个符号为什么叫walrus呢?因为把:=顺时针旋

    2024年02月05日
    浏览(41)
  • Spring中的SpEL表达式

    SpEL (Spring Expression Language) 是 Spring 框架中用于在运行时对对象图进行查询和操作的表达式语言。它可以在不引入 Java 代码的情况下,轻松地对对象进行值的计算、属性的读取、调用方法、访问数组和集合等。SpEL广泛应用于Spring框架的许多模块中,比如Spring MVC、Spring Data、Sp

    2024年02月15日
    浏览(47)
  • JavaScript中的正则表达式

    正则表达式是一种强大的工具,可以在多种编程语言中使用,包括JavaScript。它们允许您在文本中查找和匹配模式,以及执行各种操作,如替换、拆分和验证。在JavaScript中,正则表达式通常用来检查字符串是否符合某种特定模式,并且可以用来从字符串中提取特定的子字符串

    2024年02月02日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包