之前写过的文章:
canhui87:Oracle数据统计必会语法知识zhuanlan.zhihu.com
主要是告诉大家学习SQL高级编程的参考路径,现在把书里简单的实例代码整理出来,方便以后随时查询使用。深入理解还需要看书学习。
主要参考
《剑破冰山Oracle开发艺术》
《精通Oracle SQL(第2版)》
1 高级分组
grouping sets:集合汇总
grouping sets:集合汇总,对每个子集汇总后合并结果
rollup:向上汇总
cube:组合汇总
2 行转列
传统行转列
pivot 行转列
unpivot 列转行
文本聚合函数listagg()
listagg():文本聚合函数,类似wmsys.wm_concat()函数,listagg可以设置排序,wmsys.wm_concat无序
3 分析函数
分析函数结构
func() over(partition by 分组 order by 排序 滑动窗口)
常用分析函数
row_number() 唯一排序
rank() 有跳号并列排名
dense_rank() 无跳号并列排名,值并列排序
lag(字段,1,默认值) 前1行
lead(字段,1,默认值) 后1行
first_value() 第一行
last_value() 最后一行
nth_value(字段,1) 任意一行
percent_rank() 百分比排名,返回0-1间
percentile_cont() 指定百分比,返回排名值,percent_rank的反函数
percentile_disc() 类似percentile_cont(),离散分布
ntile() 分组
ratio_to_report() 计算报告中值的比例:val/sum(val) over()
活动窗口
rows between unbounded preceding and unbounded following:从第一行到最后一行,无order by时默认
rows between unbounded preceding and current row:从第一行到当前行,有order by时默认
rows between 2 preceding and 2 following:前2行到后2行
range between 100 preceding and 100 following:根据order by列的值范围,相当于加条件 where expr between a and b
KEEP语句
--KEEP :从并列排名最大最小值范围中做选择
4 递归层次查询
connect by层次查询
select level,connect_by_root|connect_by_isleaf|connect_by_iscycle
from tablename
where
connect by prior 列1=列2
start with
order siblings by
with层次查询
--with子查询CTE
with 查询名([列名])
as(子查询:锚点成员 union all 递归成员)
[遍历顺序子句:深度优先、广度优先]
[循环子句:终止循环条件]
[多个子查询]
叶子节点
--叶子节点 connect_by_isleaf伪列
构造序列
5 model子句
model子句:单元格跨行引用,类似excel
model跨行引用虽然很灵活,但语法复杂,执行效率慢,只适合在小的汇总报表使用,本人在工作上基本没使用。
6 正则表达式
正则函数
regexp_substr(字符串,正则表达式,开始位置,出现次数,匹配选项)
匹配选项,i:大小写不敏感; c:大小写敏感;n:点号 . 不匹配换行符号;m:多行模式;x:扩展模式,忽略正则表达式中的空白字符。
regexp_substr()
regexp_instr()
regexp_replace()
regexp_like()
regexp_count()
Oracle特殊字符集
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:unct:]] 任何标点符号。
[[:print:]] 任何可打印字符。
[[:space:]] 任何空白字符。
[[:xdigit:]] 任何16进制的数字,相当于[0-9a-fA-F]。
7 高级DML
insert多表插入
update关联
merge语句
8 综合实例
号段生成器
9 动态SQL
绑定变量 :x
10 PLSQL
execute immediate语句
dbms_sql包
《Oracle PLSQL 程序设计(第5版)》上下册。