Oracle递归

1.向下递归获取子节点

--根据二级获取下方的四五级节点
select * from table_name where level in('4','5')
start with id='二级节点ID'
connect by prior id=parent_id
--level:节点级别字段
--start with后定义根节点的位置
--connect by prior 本条ID等于下一条数据的parent_id

2.向上递归获取父节点 read more

Oracle over函数的简单使用

OVER() 在使用时一般会配合其他函数一起使用
1.row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号
2.rank() OVER (PARTITION BY COL1 ORDER BY COL2)
跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
3.dense_rank() OVER (PARTITION BY COL1 ORDER BY COL2)
连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的
4.lag(arg1,arg2,arg3)
arg1是从其他行返回的表达式;arg2是希望检索的当前行分区的偏移量;arg3是在arg2表示的数目超出了分组的范围时返回的值.
5.FIRST_VALUE(arg) OVER (PARTITION BY COL1 ORDER BY COL2)
返回组中数据窗口的第一个值,可以不进行order by
6.LAST_VALUE(arg) OVER (PARTITION BY COL1 ORDER BY COL2)
返回组中数据窗口的最后一个值,可以不进行order by

现在拿row_number举例:

--获取最新一条数据
select a.name from(
select name,row_number() over(partition by id order by create_date desc) as row_num
from table_name
) a where a.row_num=1;
* 根据id分组(相当于不分组),按照创建时间倒序,取第一条

row_number()多用于去重查重操作:即可根据某个字段进行排序,筛选出row_number()值大于1的

Oracle在不连表的情况下进行集合操作

--前提是两个查询的列的数量和数据类型必须完全相同

--最常用的,拼接结果集(取并集)
--去重拼接
SELECT ID FROM TABLE_A
UNION
SELECT ID FROM TABLE_B;

--不去重拼接
SELECT ID FROM TABLE_A
UNION ALL
SELECT ID FROM TABLE_B;

--取交集
SELECT ID FROM TABLE_A
INTERSECT
SELECT ID FROM TABLE_B;

--去差集
--先查第一条SQL结果集,看是否在第二条中,如果在则移除;
--再查第二条,如果在第一没有出现过,则移除
SELECT ID FROM TABLE_A
MINUS
SELECT ID FROM TABLE_B;

Oracle中的trunc函数

trunc()在操作日期时,相当于把时间格式化为零时零分零秒
select sysdate,trunc(sysdate) from dual;
通过执行以上脚本可以发现,被trunc处理过的只有日期,没有时间
trunc传参的用法:
select trunc(sysdate-1) from dual t;–昨天
select trunc(sysdate+1) from dual t;–明天

select trunc(sysdate, ‘yy’) from dual;–当年第一天
select trunc(sysdate, ‘year’) from dual;–当年第一天
select trunc(sysdate, ‘yyyy’) from dual;–当年第一天

select trunc(sysdate, ‘q’) from dual;–当前时间所在的季度的第一天

select trunc(sysdate, ‘mm’) from dual;–当月第一天
select trunc(sysdate, ‘month’) from dual;–当月第一天

select trunc(sysdate, ‘d’) from dual;–返回本周的第一天(周日为第一天)
select trunc(sysdate,’day’) from dual;–返回本周的第一天(周日为第一天)

select trunc(sysdate, ‘iw’) from dual;–本周第二天(周日为第一天)

select trunc(sysdate, ‘hh’) from dual; –当前时间,精确到小时
select trunc(sysdate, ‘hh24’) from dual;–当前时间。精确到小时

select trunc(sysdate, ‘mi’) from dual;–当前时间。精确到分钟 没有精确到秒的精度

/*
TRUNC(number,num_digits)
Number 须要截尾取整的数字。
Num_digits 用于指定取整精度的数字。
Num_digits 的默认值为 0。
正数向后截取,负数向前截取
TRUNC()函数截取时不进行四舍五入
*/
select trunc(123.458) from dual; –123
select trunc(123.458, 0) from dual; –123
select trunc(123.458, 1) from dual; –123.4
select trunc(123.458, -1) from dual; –120
select trunc(123.458, -4) from dual; –0
select trunc(123.458, 4) from dual; –123.458
select trunc(123) from dual; –123
select trunc(123, 1) from dual; –123
select trunc(123, -1) from dual; –120

* 部分内容摘自网上,记录于此仅用学习