1.union all 与union
union和union all的区别是,union会自动压缩多个结果集合中的重复结果,而union all则将所有的结果全部显示出来,不管是不是重复。
2.
Intersect:对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;
Minus:对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。
3.with as
其实就是把一大堆重复用到的SQL语句放在with as 里面,取一个别名,后面的查询就可以用它
with a as (select dummy from dual),
b as (select dummy from dual)
select a.dummy,b.dummy from a,b
where a.dummy = b.dummy
4.connect by [条件2] start with [条件3]
select num1,num2,level
from carol_tmp
start with num2=1008
connect by num2=prior num1 order by level desc;(1008向上)
select num1,num2,level
from carol_tmp
start with num2=1008
connect by prior num2= num1 order by level desc;(1008向下)
5.根据时间排重,取时间最大的值
select t.*
from (select a.*, row_number() over(partition by 需要排重的字段 order by 时间 desc) rw
from HOST_CURSTATUS a) t
where t.rw = 1
https://jingyan.baidu.com/article/9989c74604a644f648ecfef3.html
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(组内连续的唯一的)