阅读 52

Oracle · 一些实用语法

Merge 混合/合并

实现功能:当记录匹配时,更新数据,不匹配时,插入数据

Merge into table1 t1    -- 混入table1
using table2 t2         -- 用来对比的表
    on (t1.id = t2.id)      -- 对比条件
when matched then       -- 如果 匹配
    update set t1.name=t2.name
when not matched then   -- 如果 不匹配
    insert values(t2.id,t2.name)
    
## 存在这样两张表 
>>> table1
ID  |  NAME
----|-----------
 1  |  LUE
 2  |  Mark1
 
>>> table2
ID  |  NAME
----|-----------
 2  |  Mark2
 3  |  Care
 
# 执行后 table1
 ID |  NAME
----|-----------
 1  |  LUE
 2  |  Mark2
 3  |  Care
复制代码

Minus 差集

实现功能:取表1内和表2不同的数据

select * from table1 
minus
select * from table2

## 存在这样两张表 
>>> table1
ID  |  NAME
----|-----------
 1  |  LUE
 2  |  Mark1
 
>>> table2
ID  |  NAME
----|-----------
 2  |  Mark1
 3  |  Care
 
# 查询结果为 
 ID |  NAME
----|-----------
 1  |  LUE
复制代码

Intersect 交集

实现功能:两表取交集

select * from t_table1    
INTERSECT
select * from t_table2 

>>> table1
ID  |  NAME
----|-----------
 1  |  LUE
 2  |  Mark1
 
>>> table2
ID  |  NAME
----|-----------
 2  |  Mark1
 3  |  Care
 
# 查询结果为 
 ID |  NAME
----|-----------
 2  |  Mark1
复制代码

With

with newtable as
(
select 1 ,' lue ' name from dual
)
select * from newtable


# 查询结果为 
 ID |  NAME
----|-----------
 1  |  lue
复制代码

Start With 树查询

几个单词:
Start With:开始条件
Connect By ... Prior树查询条件,Prior 代表上级的值

Select * From tablename     -- 查询表
[Where ...]                 -- 满足条件的
Start With ID = '00001'     -- 从ID = ‘00001’ 开始
Connect By Prior ID = PID   -- 上级(Prior)的ID = 本级的PID(或者其他什么条件)
复制代码

Over ... Partition By 分组查询

根据 partition by 进行分组 ,组内 order by 排序

select rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t
复制代码

括号内的 partition by 、order by 之类的都可以省略,不会报错,根据业务要求各种拼接

row_number()、rank()和dense_rank() 排序号

· row_number() : 排到多少是多少,1 2 3 4 5 ... 排下去
· rank():排序时,如果相同的项会序号相同,例如:

31 、 32 、 32 、 33 、 34
# 排序后,序号为
1 2 2 4 5
复制代码

· dense_rank():排序时,如果相同的项会序号相同,和rank()区别在与 他是连续的,例如:

31 、 32 、 32 、 33 、 34
# 排序后,序号为
1 2 2 3 4
复制代码

first_value() 和 last_value() 第一行/最后一行

# srocetable 学生分数表
# name 姓名
# class 班级
# sroce 分数

# 查询 每个班 成绩最高 的 学生的姓名
select first_value(name) over(partition by class order by sroce desc) from srocetable

# 查询 每个班 成绩最低 的 学生的姓名
select last_value(name) over(partition by class order by sroce desc) from srocetable
复制代码

同理其他

count() 求分组后的总数
max() 求分组后的最大值
min() 求分组后的最小值
avg() 求分组后的平均值
lag() 取出前n行数据
lead() 取出后n行数据
ratio_to_report() 括号中就是分子,over() 括号中就是分母 (等同于 当前括号内的分子 / 分组内该列的总和) percent_rank() 百分比排名 意思是 能排到整体的百分之多少 (等同于 排名 / 总人数 )

文章分类
后端
文章标签