sql server与java实例_SQL进阶：SQL高级编程语法实例

之前写过的文章：

canhui87：Oracle数据统计必会语法知识​zhuanlan.zhihu.com

主要是告诉大家学习SQL高级编程的参考路径，现在把书里简单的实例代码整理出来，方便以后随时查询使用。深入理解还需要看书学习。

主要参考

《剑破冰山Oracle开发艺术》

《精通Oracle SQL（第2版）》

1 高级分组

grouping sets：集合汇总

grouping sets：集合汇总，对每个子集汇总后合并结果

select 

rollup：向上汇总

select 

cube：组合汇总

select 

2 行转列

传统行转列

select 

pivot 行转列

select 

unpivot 列转行

select 

文本聚合函数listagg()

listagg()：文本聚合函数，类似wmsys.wm_concat()函数，listagg可以设置排序，wmsys.wm_concat无序

select 

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

--计算工资最接近前后2名的均值

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

--传统connect by法

with层次查询

--with子查询CTE

with 查询名([列名])

as(子查询：锚点成员 union all 递归成员)

[遍历顺序子句：深度优先、广度优先]

[循环子句：终止循环条件]

[多个子查询]

--with子查询法

叶子节点

--叶子节点   connect_by_isleaf伪列

构造序列

select 

5 model子句

model子句：单元格跨行引用，类似excel

model跨行引用虽然很灵活，但语法复杂，执行效率慢，只适合在小的汇总报表使用，本人在工作上基本没使用。

select 

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语句

merge 

8 综合实例

号段生成器

--根据号码明细生成号段分组

9 动态SQL

绑定变量 :x

begin
  

10 PLSQL

execute immediate语句

dbms_sql包

《Oracle PLSQL 程序设计(第5版)》上下册。