sql case when用法

用多少眼泪才能让你相信  金牌会员 | 2024-10-8 13:18:13 | 来自手机 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 860|帖子 860|积分 2582

学习链接

SQL之CASE WHEN用法详解
CASE When的用法
CASE WHEN函数语句多条件下使用详解
case when还可以与聚合函数共同group by一起使用，达到对同一组内的数据分别统计的效果
SQL之CASE WHEN用法详解

简单CASE WHEN函数

  1. CASE SCORE WHEN 'A' THEN '优' ELSE '不及格' END
  2. CASE SCORE WHEN 'B' THEN '良' ELSE '不及格' END
  3. CASE SCORE WHEN 'C' THEN '中' ELSE '不及格' END
复制代码
等同于，使用CASE WHEN条件表达式函数实现：
  1. CASE WHEN SCORE = 'A' THEN '优'
  2.      WHEN SCORE = 'B' THEN '良'
  3.      WHEN SCORE = 'C' THEN '中' ELSE '不及格' END
复制代码
THEN后边的值与ELSE后边的值类型应同等，否则会报错。如下：
  1. CASE SCORE WHEN 'A' THEN '优' ELSE 0 END
复制代码
'优’和0数据类型不同等则报错：
[Err] ORA-00932: 数据类型不同等: 应为 CHAR, 但却获得 NUMBER
CASE WHEN条件表达式函数

简单CASE WHEN函数只能应对一些简单的业务场景，而CASE WHEN条件表达式的写法则更加灵活。
CASE WHEN条件表达式函数：雷同JAVA中的IF ELSE语句。
格式：
  1. CASE
  2.         WHEN condition THEN result
  4.         [WHEN...THEN...]
  6.         ELSE result
  7. END
复制代码
condition是一个返回布尔类型的表达式，假如表达式返回true，则整个函数返回相应result的值，假如表达式皆为false，则返回ElSE后result的值，假如省略了ELSE子句，则返回NULL。
常用场景

场景1：简单条件使用

   场景1：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回良好
  1. SELECT
  2.     STUDENT_NAME,
  3.     (CASE WHEN score < 60 THEN '不及格'
  4.         WHEN score >= 60 AND score < 80 THEN '及格'
  5.         WHEN score >= 80 THEN '优秀'
  6.         ELSE '异常' END) AS REMARK
  7. FROM
  8.     TABLE
复制代码
留意：假如你想判断score是否null的情况，WHEN score = null THEN ‘缺席测验’，这是一种错误的写法，正确的写法应为：
  1. CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END
复制代码
场景2：多目标字段统计

   场景2：现老师要统计班中，有多少男同学，多少女同学，并统计男同学中有几人及格，女同学中有几人及格，要求用一个SQL输出效果。
  表布局如下：此中STU_SEX字段，0表示男生，1表示女生。

  1. SELECT
  2.         SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT, -- 将整个表当作1个大的分组，以此类推，可以结合group by一起使用, 对分组后的每个分组这样去作聚合
  3.         SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
  4.         SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
  5.         SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
  6. FROM
  7.         THTF_STUDENTS
复制代码
输出效果如下：

场景3：经典行转列，并共同聚合函数做统计

   场景3：经典行转列，并共同聚合函数做统计
现要求统计各个都会，总共使用了多少水耗、电耗、热耗，使用一条SQL语句输出效果
  有能耗表如下：此中，E_TYPE表示能耗类型，0表示水耗，1表示电耗，2表示热耗

  1. SELECT
  2.         E_CODE,
  3.         SUM(CASE WHEN E_TYPE = 0 THEN E_VALUE ELSE 0 END) AS WATER_ENERGY,--水耗
  4.         SUM(CASE WHEN E_TYPE = 1 THEN E_VALUE ELSE 0 END) AS ELE_ENERGY,--电耗
  5.         SUM(CASE WHEN E_TYPE = 2 THEN E_VALUE ELSE 0 END) AS HEAT_ENERGY--热耗
  6. FROM
  7.         THTF_ENERGY_TEST
  8. GROUP BY
  9.         E_CODE
复制代码
输出效果如下：

场景4：CASE WHEN中使用子查询

   场景4：CASE WHEN中使用子查询
根据都会用电量多少，盘算用电成本。假设电能耗单价分为三档，根据不同的能耗值，使用相应价格盘算成本。
  价格表如下：

当能耗值小于10时，使用P_LEVEL=0时的P_PRICE的值，能耗值大于10小于30使用P_LEVEL=1时的P_PRICE的值…
  1. CASE WHEN energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 0)
  2.     WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 0) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 1)
  3.     WHEN energy > (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 1) AND energy <= (SELECT P_LIMIT FROM TABLE_PRICE WHERE P_LEVEL = 2) THEN (SELECT P_PRICE FROM TABLE_PRICE WHERE P_LEVEL = 2)
复制代码
场景5：联合max聚合函数




CASE WHEN函数语句多条件下使用详解

CASE WHEN函数语句，实现简单CASE函数和CASE搜索函数两种格式。
同时共同 SUM以及COUNT方法的使用
简单CASE函数

  1. CASE 条件参数名称
  2.     WHEN 参数值1 THEN '显示值1'
  3.     WHEN 参数值2 THEN '显示值2'
  4.     ...
  5. ELSE '显示其他值' END
复制代码
如：
  1. SELECT
  2.         orderId,
  3.         CASE state
  4.             WHEN 1 THEN '启动'
  5.             WHEN 2 THEN '关闭'
  6.         ELSE '未知状态' END AS statusName
  7. FROM t_table
复制代码
CASE搜索函数

  1. **CASE搜索函数**
  2. CASE
  3.     WHEN 条件参数名称 = '参数值1' THEN '显示值1'
  4.     WHEN 条件参数名称 = '参数值2' THEN '显示值2'
  5.     ...
  6. ELSE '显示其他值' END
复制代码
如：
  1. SELECT
  2.         orderId,
  3.         CASE
  4.             WHEN state = '1' THEN '启动'
  5.             WHEN state = '2' THEN '关闭'
  6.         ELSE '未知状态' END AS stateName
  7. FROM t_table
复制代码
这两种格式，可以实现雷同的功能。但是简单CASE函数和CASE搜索函数相比，功能方面会有些限制；
判断式

  1. SELECT
  2.         orderId,
  3.         CASE
  4.             WHEN state = '1' THEN '启动'
  5.             WHEN state = '2' THEN '关闭'
  6.             WHEN state IN ('3', '4') THEN '待解锁'
  7.         ELSE '未知状态' END AS stateName
  8. FROM t_table
复制代码
若是多重时，WHEN 的 IN 条件中的值和下一个 WHEN 的条件重合会被忽略，如下面的 “关闭” 状态会被忽略，永远无法得到 “关闭”
  1. SELECT
  2.         orderId,
  3.         CASE
  4.             WHEN state = '1' THEN '启动'
  5.             WHEN state IN ('2', '3', '4') THEN '待解锁'
  6.             WHEN state = '2' THEN '关闭'
  7.         ELSE '未知状态' END AS stateName
  8. FROM t_table
复制代码
同时共同 SUM 以及 COUNT 方法的使用

① SUM函数

  1. **简单CASE函数**
  2. SUM(CASE 条件参数名称
  3.             WHEN 参数值 THEN '显示被求和值'
  4.         ELSE 0 END
  5.         ) AS SUMAMT
  8. **CASE搜索函数**
  9. SUM(CASE
  10.             WHEN 条件参数名称 = '参数值' THEN '显示被求和值'
  11.         ELSE 0 END
  12.         ) AS SUMAMT
  15. **或者**       
  16. SUM(CASE
  17.             WHEN
  18.                     条件参数名称1 = '参数值1' AND 条件参数名称2 = '参数值2' ...
  19.             THEN '显示被求和值'
  20.         ELSE 0 END
  21.         ) AS SUMAMT
  22.        
  24. **或者**       
  25. SUM(CASE
  26.             WHEN
  27.                     条件参数名称1 IN ('参数值n') AND 条件参数名称2 = '参数值2' ...
  28.             THEN '显示被求和值'
  29.         ELSE 0 END
  30.         ) AS SUMAMT
复制代码
示例： 根据时间条件查询值，有值时表现 “被求和值”， 没有值时，表现 “null”
状态：state
类型：t_type
金额：amt
  1. **简单CASE函数**
  2. SELECT
  3.         SUM(CASE state
  4.             WHEN 1 THEN amt
  5.         ELSE 0 END
  6.         ) AS sumAmt
  7. FROM
  8.         t_table
  9. WHERE
  10.         core_time >= '2020-07-01 00:00:00'
  11. AND core_time <= '2020-07-20 23:59:59'
  12. GROUP BY core_time
  15. **CASE搜索函数**
  16. SELECT
  17.         SUM(CASE
  18.             WHEN state = '1' THEN amt
  19.         ELSE 0 END
  20.         ) AS sumAmt
  21. FROM
  22.         t_table
  23. WHERE
  24.         core_time >= '2020-07-01 00:00:00'
  25. AND core_time <= '2020-07-20 23:59:59'
  26. GROUP BY core_time
  29. **或者**
  30. SELECT
  31.         SUM(CASE
  32.             WHEN state = '1' AND t_type = '2' THEN amt
  33.         ELSE 0 END
  34.         ) AS sumAmt
  35. FROM
  36.         t_table
  37. WHERE
  38.         core_time >= '2020-07-01 00:00:00'
  39. AND core_time <= '2020-07-20 23:59:59'
  40. GROUP BY core_time
  43. **或者**
  44. SELECT
  45.         SUM(CASE
  46.             WHEN state IN ('2', '3', '4') AND t_type = '2' THEN amt
  47.         ELSE 0 END
  48.         ) AS sumAmt
  49. FROM
  50.         t_table
  51. WHERE
  52.         core_time >= '2020-07-01 00:00:00'
  53. AND core_time <= '2020-07-20 23:59:59'
  54. GROUP BY core_time
复制代码
② COUNT函数

  1. **简单CASE函数**
  2. COUNT(CASE 条件参数名称
  3.             WHEN 参数值 THEN 1
  4.         ELSE 0 END
  5.         ) AS COUNTAMT
  8. **CASE搜索函数**
  9. COUNT(CASE
  10.             WHEN 条件参数名称 = '参数值' THEN 1
  11.         ELSE 0 END
  12.         ) AS COUNTAMT
  15. **或者**       
  16. COUNT(CASE
  17.             WHEN
  18.                     条件参数名称1 = '参数值1' AND 条件参数名称2 = '参数值2' ...
  19.             THEN 1
  20.         ELSE 0 END
  21.         ) AS COUNTAMT
  23.        
  24. **或者**       
  25. COUNT(CASE
  26.             WHEN
  27.                     条件参数名称1 IN ('参数值n') AND 条件参数名称2 = '参数值2' ...
  28.             THEN 1
  29.         ELSE 0 END
  30.         ) AS COUNTAMT
复制代码
示例： 根据时间条件查询值，有值时表现 “总条数值”， 没有值时，表现 “0”
状态：state
类型：t_type
金额：amt
  1. **简单CASE函数**
  2. SELECT
  3.         COUNT(CASE state
  4.             WHEN 1 THEN 1
  5.         ELSE 0 END
  6.         ) AS countAmt
  7. FROM
  8.         t_table
  9. WHERE
  10.         core_time >= '2020-07-01 00:00:00'
  11. AND core_time <= '2020-07-20 23:59:59'
  12. GROUP BY core_time
  15. **CASE搜索函数**
  16. SELECT
  17.         COUNT(CASE
  18.             WHEN state = '1' THEN 1
  19.         ELSE 0 END
  20.         ) AS countAmt
  21. FROM
  22.         t_table
  23. WHERE
  24.         core_time >= '2020-07-01 00:00:00'
  25. AND core_time <= '2020-07-20 23:59:59'
  26. GROUP BY core_time
  29. **或者**
  30. SELECT
  31.         COUNT(CASE
  32.             WHEN state = '1' AND t_type = '2' THEN 1
  33.         ELSE 0 END
  34.         ) AS countAmt
  35. FROM
  36.         t_table
  37. WHERE
  38.         core_time >= '2020-07-01 00:00:00'
  39. AND core_time <= '2020-07-20 23:59:59'
  40. GROUP BY core_time
  43. **或者**
  44. SELECT
  45.         COUNT(CASE
  46.             WHEN state IN ('2', '3', '4') AND t_type = '2' THEN 1
  47.         ELSE 0 END
  48.         ) AS countAmt
  49. FROM
  50.         t_table
  51. WHERE
  52.         core_time >= '2020-07-01 00:00:00'
  53. AND core_time <= '2020-07-20 23:59:59'
  54. GROUP BY core_time
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

用多少眼泪才能让你相信

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表