Oracle · 一些实用语法

小小码 • 2023-12-14 13:30 • 未分类 • 阅读 156

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说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() 百分比排名意思是能排到整体的百分之多少（等同于排名 / 总人数）