【Oracle】函数语法学习记录

一、基础语法

1、CTE：公用表表达式

 语法：With 表名 as (查询语句)

 作用：将一段查询结果作为公用表，在该条完整SQL中能够使用公用表的查询结果进行查询

 示例：

with aa as( 
select bf.name,sum(b.tot_amt) amt,sum(b.tot_num) num 
from b_fo b,b_funit bf 
where b.b_funit_id=bf.id 
 and b.tot_num>0 
group by bf.name 
) 
select name,num,amt from aa 
union all 
select '合计',sum(num),sum(amt) from aa ​

运行效果如下图

2、HAVING

语法：接在SQL最后

作用：在使用了where表达式之后，没办法进和合计函数一起使用。该表达式解决此问题

示例：

select bf.name,sum(b.tot_amt) amt,sum(b.tot_num) num
from b_fo b,b_funit bf 
where b.b_funit_id=bf.id
     and b.tot_num>0
group by bf.name
Having sum(b.tot_amt) >1000000 

--判断查询标准金额大于XX的数据

3、union

语法：union 和 union all

作用：用于拼接两条查询结果集，要求两条查询结果的列数和数据类型需要一致

示例：

1. union：出现重复的数据，只会显示一条

select bf.name,b.tot_num,b.tot_amt from b_fo b,b_funit bf 
where b.b_funit_id=bf.id
and b.tot_num>0
union
select bf.name,b.tot_num,b.tot_amt from b_fo b,b_funit bf 
where b.b_funit_id=bf.id
and b.tot_num>0

查询结果如下图

2.union all：显示所有查询结果

select bf.name,b.tot_num,b.tot_amt from b_fo b,b_funit bf 
where b.b_funit_id=bf.id
and b.tot_num>0
union all
select bf.name,b.tot_num,b.tot_amt from b_fo b,b_funit bf 
where b.b_funit_id=bf.id
and b.tot_num>0  

查询结果如下图：

4、nvl(字符串,替换函数)

用于判断指定数据结果为NULL时，使用什么字符替换显示

select nvl(phone,'未维护手机号') from users

5、pivot（）

用来进行行转列的操作

select * from (select bf.qty,m.column1 from b_foitem bf,m_product m 
where bf.m_product_id=m.id)
pivot(sum(qty) for column1 in ('上装' AS top,'下装' AS LOW))    

比如如上代码的输出结果为

当我们需要将某个字段每一个值都按照列来显示时，使用这个函数来进行转换。

语法：pivot(聚合字段 for 转列字段 in (取别名))

注意：

1.这个函数中 for前面的一定是一个聚合函数包裹起来的

2.这个函数不能接在where那些后面。只能够直接接在from表的后面

3.in（）里面的字段需要使用一个括号括起来，并且里面取别名时，原字段需要用’'框起来。别名则不需要

6.decode(if,条件1)

decode相当于一个简单的if判断条件

select SUM(bf.qty),decode(m.column1,'上装',12,'下装',55,88) from b_foitem bf,m_product m 
where bf.m_product_id=m.id
group by m.column1

#解释：当column1等于上装的时候查询结果集为12，为下装的时候结果集为55，否则就是88

最终输出结果为

7.查询语句中增加合计维度

 SELECT
	decode(grouping (dim5.attribname),1,'-',nvl( dim5.attribname, '其它' )) 大类,
	decode(grouping ( dim6.attribname ),1,dim5.attribname || '合计',nvl( dim6.attribname, '其它' )) 中类,
	--grouping和grouping_id 区别
	/*
	grouping 中的参数只能够存放一列。如果在多维护合计时不太好用。如果是合计行返回值为1，否则为0
	grouping_id 中的参数可以存放多个.返回值是2进制转换为10进制的数
	        举例： 比如我有三个维度，我需要给一个总的合计，那么对应的grouping_id出来的返回值应该是
	           二进制数：   1 1 1
	           十进制数：   4+2+1 = 7
	*/
	sum( bf.qty ) 数量 
FROM
	b_foitem bf,
	m_product m,
	m_dim dim5,
	m_dim dim6 
WHERE
	bf.m_product_id ( + ) = m.id 
	AND m.m_dim5_id = dim5.id ( + ) 
	AND m.m_dim6_id = dim6.id ( + ) 
GROUP BY
	--grouping sets ( dim5.attribname, dim6.attribname ) 取得期望值。也就是只显示合计值
	--cube ( dim5.attribname, dim6.attribname ) 会将所填写的几种合计方式汇总在一个查询结果集里面返回
	rollup ( dim5.attribname, dim6.attribname ) --根据维度合计，保留一个结果集
ORDER BY
	dim5.attribname

1.分维度合计是根据 rollup()，括号里面填写合计维度

2.在from 前面的grouping()是提供的函数，能够将合计的NULL返回一个1，方便我们判断显示合计的提示

8. 某个字段需要使用拼接符拼接成为一条数据

listagg(name,',') within group(order by name)

使用listagg(字段,分隔符) within group(order by 字段)

示例：

select listagg(name,',') within group(order by name) from ad_table where description like '%起订量%';

 将描述包含起订量的名称，使用逗号拼接起来
 比如需要获取尺码颜色中每个尺码组有哪些数据，并使用逗号隔开

select attribname,listagg(value,',') within group(order by orderno) from I_CLRSIZE where attribname <> '颜色' group by attribname​

9. case when 用法介绍

case when 相当于在Java中的if else，当满足条件时返回某个结果

select name,
 case
 when attribname = 'A' then
 '这个是A'
 when attribname = 'B' then
 '这个是B'
 else
 '不知道是谁'
 end
from m_product​