在工作中,日常的数据库开发,其实大部分用到的数据库知识并不复杂,无非是CRUD【增删改查】,但是偶尔会有一些特殊的需求,看似合理,但是一时半会儿也想不起来如何下手,所以只能去百度查找。为了方便起见,这里列举了一些工作中日常用到但又稍微复杂的语句,仅供学习分享使用。
分区排序【partition by】
按指定列分组,同时另一列排序。如:成绩表中,按班级分组,成绩排序。排出每一班级的成绩顺序。
分区排序语法:
select row_number()[rank(),dense_rank()] OVER (PARTITION BY 分组字段1,分组字段2 ORDER BY 排序字段1) from table;
注意:此处不可以用group by ,因为group by 是分组进行汇总功能。
row_number示例:
select sno,cno,degree,
row_number()over(partition by cno order by degree desc) mm
from score
rank示例:
SELECT *
FROM (select sno,cno,degree,
rank()over(partition by cno order by degree desc) mm
from score)
WHERE mm = 1;
rank和row_number的区别
由以上的例子得出,在求第一名成绩的时候,不能用row_number(),因为如果同班有两个并列第一,row_number()只返回一个结果。具体差异如下:
-
rownumber函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
-
rank函数返回一个唯一的值,除非遇到相同的数据,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)。
-
dense_rank函数返回一个唯一的值,除非当碰到相同数据,此时所有相同数据的排名都是一样的。dense_rank()是连续排序,有两个第二名时仍然跟着第三名。他和row_number的区别在于row_number是没有重复值的。
递归查询【start with】
如果表中存在层次数据,则可以使用层次化查询子句查询出表中行记录之间的层次关系。如:在一个表中,有两个字段:id,父id,则递归查询的意思是循环查询出具有递归关系的数据。
语法:
[ START WITH CONDITION1 ]
CONNECT BY [ NOCYCLE ] CONDITION2
[ NOCYCLE ]
start with 子句为可选项,用来标识哪行作为查找树型结构的第一行(即根节点,可指定多个根节点)。若该子句被省略,则表示所有满足查询条件的行作为根节点。(PRIOR置于运算符前后的位置,决定着查询时的检索顺序)
1. 从根节点自顶向下
select empno, mgr, level as lv
from scott.emp a
start with mgr is null
connect by (prior empno) = mgr
order by level;
分析:
层次查询执行逻辑:
-
确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
-
从上一行出发,扫描除该行之外所有数据行。
-
匹配条件 (prior empno) = mgr
注意:
一元运算符 prior,意思是之前的,指上一行
当前行定义:步骤2中扫描得到的所有行中的某一行
匹配条件含义:当前行字段 mgr 的值等于上一行字段 empno中的值,若满足则取出该行,并将level + 1
匹配完所有行记录后,将满足条件的行作为上一行,执行步骤 2,3。直到所有行匹配结束。
2. 从根节点自底向上
select empno, mgr, level as lv
from scott.emp a
start with empno = 7876
connect by (prior mgr ) = empno
order by level;
分析:
层次查询执行逻辑:
-
确定上一行(相对于步骤b中的当前行),若start with 子句存在,则以该语句确定的行为上一行,若不存在则将所有的数据行视为上一行。
-
从上一行出发,扫描除该行之外所有数据行。
-
匹配条件 (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函数的语法结构如下:
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
listagg虽然是聚合函数,但可以提供分析功能(比如可选的OVER()子句)。使用listagg中,下列中的元素是必须的:
-
需要聚合的列或者表达式
-
WITH GROUP 关键词
-
分组中的ORDER BY子句
示例:
SELECT deptno,LISTAGG(ename, ',') WITHIN GROUP (ORDER BY deptno) AS employees
FROM emp GROUP BY deptno;
拆分字符串成多行【REGEXP_SUBSTR】
有一个问题,需要把一个带有,的字符串拆分成多行。通过查询资料,这个操作需要使用以下2个关键知识:
-
REGEXP_SUBSTR函数
-
为了实现动态参数,使用 connect by
REGEXP_SUBSTR语法:
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
参数说明:
__srcstr :需要进行正则处理的字符串
__pattern :进行匹配的正则表达式
__position :起始位置,从第几个字符开始正则表达式匹配(默认为1)
__occurrence :标识第几个匹配组,默认为1
__modifier :模式('i'不区分大小写进行检索;'c'区分大小写进行检索。默认为'c'。)
示例:
select regexp_substr('1,2,3','[^,]+',1,1) result from dual;
select regexp_substr('1,2,3','[^,]+',1,2) result from dual;
可以通过connect by可以构造连续的值。如下所示:
select rownum from dual connect by rownum<=7;
结合REGEXP_SUBSTR 及 connect by 即可实现拆分字符串为多行的需求,最终的语句为:
SELECT REGEXP_SUBSTR('1,2,3', '[^,]+', 1, rownum)
FROM dual
CONNECT BY ROWNUM <= LENGTH('1,2,3') - LENGTH(regexp_replace('1,2,3', ',', '')) + 1;
有则修改,无则插入【merge into】
当我们对一个表中数据执行操作:如果存在,进行修改;如果不存在,进行插入。此种情况下,采用merge into 语句最为合适。
merge info语法:
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
WHEN MATCHED THEN
[UPDATE sql]
WHEN NOT MATCHED THEN
[INSERT sql]
merge into作用:
判断B表和A表是否满足ON中条件,如果满足则用B表去更新A表,如果不满足,则将B表数据插入A表但是有很多可选项,如下:
-
正常模式
-
只update或者只insert
-
带条件的update或带条件的insert
-
全插入insert实现
-
带delete的update(觉得可以用3来实现)
merge into示例:
merge into score a
using (select std_no, c.dept_no
from student c
where c.std_no in
(select std_no from tmp_20210809)) b
on (a.std_no = b.std_no and a.balb_type = '01')
when matched then
update set a.pre_bal = nvl(a.pre_bal, 0) + 5.8
WHEN NOT MATCHED THEN
insert
(a.bal_id, a.std_no, a.balb_type, a.pre_bal, a.dept_no)
values
(序列, b.std_no, '01', 5.8, b.dept_no);