1.row_number() over(partition by 分组参数 order by 排序参数 [desc])
select t.*,
(row_number() over(partition by TO_CHAR(T.CREATE_TIME,'yyyyMMdd') order by t.CREATE_TIME DESC)) rn
from t_table t
where create_time > trunc(sysdate)
--今日内,根据CREATE_TIME格式化后日期的分组倒叙查询表 t_table
--得到的rn就是数据的排序序号,数据量大的话会很慢
2.wm_concat(拼接多个参数) 子查询用
select
(select TO_CHAR(wm_concat(roleid))
from userroles where userid = t.id) roles,
t.*
from user t
--查询用户的所有权限,拼接成一个字段,用逗号隔开
3.--单个字段拆分多行
--方法一
select distinct id,xm,trim(regexp_substr(i.lxdh, '[^,]+', 2, level)) sjhm
from table_test i
connect by level <=
(length(i.lxdh) -
length(regexp_replace(i.lxdh, ',', ''))+1 ) order by id
--方法二
select distinct regexp_substr('1,2,3,4,5,6','[^,]+',1,level) from dual
connect by level<=regexp_count('1,2,3,4,5,6','[^,]+')