1 前言:
在显示业务中经常会有如果某一个值是 A的情况下执行A条件的查询sql,当这个值是B的时候执行B条件下的查询sql,这样的场景就不得不用到分支控制语句(函数),
第一部分 : 三种分支函数基础方法介绍
第二部分 : 使用分支函数进行行转列
第三部分 : 使用分支函数减少统计时查询表消耗
- if-then-else
- decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
- case when
注:WITH t1 AS是创建子查询,相当于创建视图
第一部分 : 三种分支函数基础方法介绍
2 if-then-else
IF-THEN语句的序列之后的ELSE语句的可选序列,ELSE语句块在IF条件为FALSE时执行。语法IF-THEN-ELSE语句的语法是 -
IF condition THEN
S1;
ELSE
S2;
END IF;
其中,S1和S2是不同的语句序列。 在IF-THEN-ELSE语句中,当测试条件 condition 为TRUE时,执行语句S1并跳过S2; 当测试条件 condition 为FALSE时,则跨过S1并执行语句S2中的语句块。 例如 -
IF color = red THEN
'You have chosen a red car'
ELSE
Please choose a color for your car';
END IF;
因为if-then-else是一个二维的分支控制语句,有其局限性,所以一般使用这个语句做分支控制还是比较少的
3 decode()
decode (条件, 值1, 返回值1, 值2, 返回值2,…值n,返回值n,缺省值)
eg : decode(code , ‘001’ , code, ‘002’ , NAME, salary)
解析 :
当code 等于’001’ 返回code的值,当code 等于’002’ 返回NAME的值,没有等于的值则返回salary
3.1含义解释:
当字段或字段的运算的值等于 值1时,该函数返回 返回值1,当字段或字段的运算的值等于 值2时,该函数返回 返回值2 … 如果所有值都没有匹配上则返回默认的缺省值.
当然值1,值2,值3也可以是表达式,这个函数使得分支控制语句简单了许多
执行SQL :
WITH t1 AS
(
SELECT '001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
SELECT
decode(code , '001' , code,
'002' , NAME,
salary) AS obj
from t1
3.2 解析 :
当code 等于’001’ 返回t1表中code的值,当code 等于’002’ 返回t1表中NAME的值,没有等于的值则返回t1表中salary
4 case when
4.1 Case具有两种格式。简单Case函数和Case搜索函数。**
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
4.2 以下解析搜索函数
搜索函数 执行SQL:
WITH t1 AS
(
SELECT
'1' as objId,
'001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
SELECT
(case when code = '001' then objId
when code = '002' then NAME
else (salary || '') end
)
as obj
FROM t1
解析 :
当code = ‘001’ 取t1表中 objId的值,当 code = ‘002’ 取t1表中NAME的值,如果都没有匹配到值则取,注意else后面的 || ‘’ ,case后的其他类型都是字符串类型,salary是number类型,如果不拼上字符串,该语句会报错,说明case when相对于decode()有严格的类型控制
第二部分 :使用分支函数进行行转列
5 为什么要使用行转列
存储的数据是一对多数据,比如在某宝上买了一个商品,评价的时候有商品评价评分,满意度评价评分,物流评分和一个语言评价四个维度,那在某个表中肯定是一个相同的商品id存储了对应的四份数据,
而对于统计或者数据导出的时候我只是一条订单只关心他的 商品评价多少分,满意度多少分,物流多少分,评语,那么同一个商品id的四条行数据,我如何只取一行中的一个或者几个字段组成新的列呢?
这种情况就需要用到行转列
5.1 使用case when进行行转列
执行sql:
WITH t1 AS
(
SELECT
'1' as objId,
'001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
SELECT
max(case when code = '001' then objId ELSE '' end) as objId,
max(case when code = '001' then code ELSE '' end) as code,
max(case when code = '002' then NAME ELSE '' end) as NAME,
max(case when code = '003' then salary ELSE 0 end) as salary
FROM t1
5.2 解析
这里怎么看出是行转列呢?注意每一行前的max()函数,把上面SQL的每一行的max函数去掉在执行会得到如下结果,可以看出每一行都有一个目标值,除了目标值之外的其他都是null值,而使用max()可以自然的过滤出每次我们要的非空目标值,从而将三行数据合并为一行数据,当然,有兴趣的同学可以使用decode()实现一下
第三部分 :使用分支函数减少统计时查询表消耗
6 使用分支函数减少统计时查询表消耗
6.1 要求 : 现有如下数据,需要统计出其中最大的 code值和所有的数据的salary总和
数据 SQL:
WITH t1 AS
(
SELECT
'1' as objId,
'001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
select * from t1
6.2正常的查询逻辑SQl
正常的查询sql就是查两次t1表,然后使用 Union All进行数据的组合或者在外层包一层 Select * From 查询语句进行数据组合,如下面的sql就是包一层select 查询语句进行数据组合.但是这样有一个问题,就是有一个统计函数(如MAX(),SUM()等需要全表扫描的函数)就需要全表扫描一次,那假如一个表数据达到10万条,有5个统计函数,那么就需要全表查询50万次,那么这这么一条语句的查询消耗就太高了
正常的查询逻辑 执行SQL:
WITH t1 AS
(
SELECT
'1' as objId,
'001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
(
select
(select sum(salary) from t1) as salary,
(select max(code) from t1) as code
from
dual
)
6.2使用case when的查询逻辑SQl
执行SQL:
WITH t1 AS
(
SELECT
'1' as objId,
'001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '1' as objId,
'003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
SELECT
objId,
sum(case when 1=1 then salary ELSE 0 end) as salary,
MAX(case when 1=1 then code ELSE '' end) as code
FROM t1
group BY
objId
6.3 结论
可以看出,使用case when在一次查表数据之后就把每一次查询的一条数据分别匹配每一个统计函数,所以不管之后有多少统计函数,查询的数量永远也只是全表扫描一次,当然,有兴趣的同学可以使用decode()实现一下
7 扩展 使用 WITH XX AS 定义子查询部分减少统计时查询表消耗
7.1 WITH AS的含义
WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
7.2 这个with XX as 是不是感觉很眼熟
没错,就是之前使用的在没有任何建表语句,但是有表数据的 WITH t1 AS ,
只要记住需要在后面的查询中需要频繁用到的需要查询的数据可以统一用 WITH t1 AS 查出,然后后面的查询使用 from t1的数据,那么所有的查询数据就可以共享,而不要频繁进行查表操作
这里给一个使用 WITH t1 AS 组合 decode()实现的减少查询消耗的例子,
具体的其他的 WITH XX AS使用方法请看这 SQL With As 用法
WITH t1 AS 组合 decode()
执行SQL :
WITH t1 AS
(
SELECT '001' AS code,
'aa' AS NAME,
500 AS salary FROM dual
UNION ALL
SELECT '002' AS code,
'bb' AS NAME,
1000 AS salary FROM dual
UNION ALL
SELECT '003' AS code,
'cc' AS NAME,
300 AS salary FROM dual
)
SELECT
decode('' , '' , (SELECT sum(salary) FROM t1)) AS salary,
decode('' , '' , (SELECT max(code) FROM t1)) AS code,
(SELECT '2' FROM dual )AS status
from dual
7.2 结论
因为with XX as 的使用,所以我多次在decode()中多次使用 from t1是没关系的,因为需要统计的数据已经全部查到了t1表中,而这样也同样实现了 统计出其中最大的 code值和所有的数据的salary总和 的要求
参考文章:
PL/SQL IF-THEN-ELSE语句
Oracle 中 decode 函数用法
CASE WHEN 及 SELECT CASE WHEN的用法