在工作中,日常的数据库开发,其实大部分用到的数据库知识并不复杂,无非是CRUD【增删改查】,但是偶尔会有一些特殊的需求,看似合理,但是一时半会儿也想不起来如何下手,所以只能去百度查找。为了方便起见,这里列举了一些工作中日常用到但又稍微复杂的语句,仅供学习分享使用。如有不足之处,还请指正。

分区排序【partition by】

按指定列分组,同时另一列排序。如:成绩表中,按班级分组,成绩排序。排出每一班级的成绩顺序。

分区排序语法:

1 select row_number()[rank(),dense_rank()] OVER (PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1) from table;

注意:此处不可以用group by ,因为group by 是分组进行汇总功能。

row_number示例:

1 select sno,cno,degree,
2        row_number()over(partition by cno order by degree desc) mm 
3        from score

 rank示例:

1 SELECT    * 
2 FROM    (select sno,cno,degree,
3           rank()over(partition by cno order by degree desc) mm 
4           from score) 
5 where mm = 1;

rank和row_number的区别

由以上的例子得出,在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。具体差异如下:

  1. rownumber函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 
  2. rank函数返回一个唯一的值,除非遇到相同的数据,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)。
  3. dense_rank函数返回一个唯一的值,除非当碰到相同数据,此时所有相同数据的排名都是一样的。dense_rank()是连续排序,有两个第二名时仍然跟着第三名。他和row_number的区别在于row_number是没有重复值的。

递归查询【start with】

如果表中存在层次数据,则可以使用层次化查询子句查询出表中行记录之间的层次关系。如:在一个表中,有两个字段:id,父id,则递归查询的意思是循环查询出具有递归关系的数据。

语法:

1 [ START WITH CONDITION1 ]
2 CONNECT BY [ NOCYCLE ] CONDITION2
3 [ NOCYCLE ]

start with 子句为可选项,用来标识哪行作为查找树型结构的第一行(即根节点,可指定多个根节点)。若该子句被省略,则表示所有满足查询条件的行作为根节点。2.关于PRIOR PRIOR置于运算符前后的位置,决定着查询时的检索顺序。 

1. 从根节点自顶向下

1 select empno, mgr, level as lv
2 from scott.emp a
3 start with mgr is null
4 connect by (prior empno) = mgr
5 order by level;

--分析
层次查询执行逻辑:

  1. 确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
  2. 从上一行出发,扫描除该行之外所有数据行。
  3. 匹配条件 (prior empno) = mgr

注意:

一元运算符 prior,意思是之前的,指上一行

当前行定义:步骤2中扫描得到的所有行中的某一行

匹配条件含义:当前行字段 mgr 的值等于上一行字段 empno中的值,若满足则取出该行,并将level + 1,

匹配完所有行记录后,将满足条件的行作为上一行,执行步骤 2,3。直到所有行匹配结束。

2. 从根节点自底向上

1 select empno, mgr, level as lv
2 from scott.emp a
3 start with empno = 7876
4 connect by (prior mgr ) = empno
5 order by level;

--分析
层次查询执行逻辑:

  1. 确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
  2. 从上一行出发,扫描除该行之外所有数据行。
  3. 匹配条件 (prior mgr ) = empno

注意:

一元运算符 prior,意思是之前的,指上一行。
当前行定义:步骤2中扫描得到的所有行中的某一行。
匹配条件含义:当前行字段 empno 的值等于上一行字段 mgr 中的值,若满足则取出该行,并将 level + 1,
匹配完所有行记录后,将满足条件的行作为上一行,执行步骤2,3。直到所有行匹配结束。

3. 递归查询总结

自顶向下,自下向上口诀:
start with child_id = 10 connect by (prior child_id) = parent_id
prior 和 子列在一起,表示寻找它的子孙,即自顶向下,和父列在一起,表示开始寻找它的爸爸,即自下向上。

一列多行转换成一行【listagg】

 LISTAGG是Oracle 11g推出的,listagg函数的语法结构如下:

1 LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

 listagg虽然是聚合函数,但可以提供分析功能(比如可选的OVER()子句)。使用listagg中,下列中的元素是必须的:

  1. 需要聚合的列或者表达式 
  2. WITH GROUP 关键词 
  3. 分组中的ORDER BY子句

示例:

1 SELECT deptno,LISTAGG(ename, ',') WITHIN GROUP (ORDER BY deptno) AS employees FROM  emp GROUP BY deptno;

拆分字符串成多行【REGEXP_SUBSTR】

有一个问题,需要把一个带有,的字符串拆分成多行。通过查询资料,这个操作需要使用以下2个关键知识:

  1. REGEXP_SUBSTR函数
  2. 为了实现动态参数,使用 connect by

REGEXP_SUBSTR语法:

1 function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

参数说明:

__srcstr :需要进行正则处理的字符串

__pattern :进行匹配的正则表达式

__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)

__occurrence :标识第几个匹配组,默认为1

__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)

示例:

1 select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
2 select regexp_substr('1,2,3','[^,]+',1,2) result from dual;

可以通过connect by可以构造连续的值。如下所示:

1 select rownum from dual connect by rownum<=7;

 

结合REGEXP_SUBSTR 及 connect by 即可实现拆分字符串为多行的需求,最终的语句为: 

1 SELECT REGEXP_SUBSTR ('1,2,3', '[^,]+', 1,rownum)
2 from dual connect by rownum<=LENGTH ('1,2,3') - LENGTH (regexp_replace('1,2,3', ',', ''))+1;

有则修改,无则插入【merge into】

当我们对一个表中数据执行操作:如果存在,进行修改;如果不存在,进行插入。此种情况下,采用merge into 语句最为合适。

merge info语法:

1 MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
2  
3 WHEN MATCHED THEN
4  
5 [UPDATE sql]
6  
7 WHEN NOT MATCHED THEN
8  
9 [INSERT sql]

merge into作用:

判断B表和A表是否满足ON中条件,如果满足则用B表去更新A表,如果不满足,则将B表数据插入A表但是有很多可选项,如下:

  1. 正常模式
  2. 只update或者只insert
  3. 带条件的update或带条件的insert
  4. 全插入insert实现
  5. 带delete的update(觉得可以用3来实现)

merge into示例:

 1 merge into score a
 2 using (select std_no, c.dept_no
 3          from student c
 4         where c.std_no in
 5               (select std_no from tmp_20210809)) b
 6 on (a.std_no = b.std_no and a.balb_type = '01')
 7 when matched then
 8   update set a.pre_bal = nvl(a.pre_bal, 0) + 5.8
 9 WHEN NOT MATCHED THEN
10   insert
11     (a.bal_id, a.std_no, a.balb_type, a.pre_bal, a.dept_no)
12   values
13     (序列, b.std_no, '01', 5.8, b.dept_no);

 备注

在这个世上,根本就没有所谓的一蹴而就。只有日积月累的努力,才有厚积薄发的可能。请沉下心来,不要好高骛远,也不要总是去艳羡别人。专心做好自己的事,当你的才华配得上梦想时,好运自会不期而遇。


作者:Alan.hsiang
出处:http://www.cnblogs.com/hsiang/
本文版权归作者和博客园共有,写文不易,支持原创,欢迎转载【点赞】,转载请保留此段声明,且在文章页面明显位置给出原文连接,谢谢。
关注个人公众号,定时同步更新技术及职场文章

posted on 2021-10-17 15:26  Alan.hsiang  阅读(237)  评论(0编辑  收藏  举报