--create by liuchengyuan
-- 一.row_number(),rank,dense_rank(),ntile()用法
-- 四个都是排序编号,
-- row_number()按顺序编号,如果所排序字段值相同,则产生的行号可能不同
-- rank为按排序字段分组,每组序号为各组第一行的当前行号
-- dense_rank为按排序字段分组,每组序号为各组的实际排序序号
-- ntile(n) 为按排序字段分组,把数据分成n份,每份为 总数据行/n,如果剩余,则按顺序从上到下增加一行,直到增加完为止
-- 所以每份不一定相等,但最多相差一行.可用于归类
select row_number() over ( order by dept_id desc ) as row_id,
rank() over ( order by dept_id desc ) as rk_id,
dense_rank() over ( order by dept_id desc ) as drk_id,
ntile( 5 ) over ( order by dept_id) as ntile_id,
dept_id, user_id
from usermnt where dept_id in ( ' dept121 ' , ' dept126 ' , ' dept127 ' , ' dept179 ' , ' dept63 ' )
order by dept_id desc ;
-- 如下例子
row_id rk_id drk_id ntile_id dept_id user_id
-- ------------------ -------------------- -------------------- -------------------- ---------- ----------
1 1 1 5 DEPT179 YAOZY
2 2 2 4 DEPT127 LHT
3 2 2 4 DEPT127 AMYCHAN
4 4 3 3 DEPT126 CY
5 5 4 1 DEPT121 LINA
6 5 4 1 DEPT121 PENGYANG
7 5 4 2 DEPT121 SHIRLEY
8 5 4 2 DEPT121 LUNU
9 5 4 3 DEPT121 DEAN
( 9 row(s) affected)
-- 二,CET with
-- 递归
with dept_cet(id,lv)
as
( select id, 0
from department where top_dept = ' Y '
union all
select a.id ,lv + 1
from department as a join
dept_cet as b
on a.superior_dept = b.id)
select * from dept_cet
-- 一.row_number(),rank,dense_rank(),ntile()用法
-- 四个都是排序编号,
-- row_number()按顺序编号,如果所排序字段值相同,则产生的行号可能不同
-- rank为按排序字段分组,每组序号为各组第一行的当前行号
-- dense_rank为按排序字段分组,每组序号为各组的实际排序序号
-- ntile(n) 为按排序字段分组,把数据分成n份,每份为 总数据行/n,如果剩余,则按顺序从上到下增加一行,直到增加完为止
-- 所以每份不一定相等,但最多相差一行.可用于归类
select row_number() over ( order by dept_id desc ) as row_id,
rank() over ( order by dept_id desc ) as rk_id,
dense_rank() over ( order by dept_id desc ) as drk_id,
ntile( 5 ) over ( order by dept_id) as ntile_id,
dept_id, user_id
from usermnt where dept_id in ( ' dept121 ' , ' dept126 ' , ' dept127 ' , ' dept179 ' , ' dept63 ' )
order by dept_id desc ;
-- 如下例子
row_id rk_id drk_id ntile_id dept_id user_id
-- ------------------ -------------------- -------------------- -------------------- ---------- ----------
1 1 1 5 DEPT179 YAOZY
2 2 2 4 DEPT127 LHT
3 2 2 4 DEPT127 AMYCHAN
4 4 3 3 DEPT126 CY
5 5 4 1 DEPT121 LINA
6 5 4 1 DEPT121 PENGYANG
7 5 4 2 DEPT121 SHIRLEY
8 5 4 2 DEPT121 LUNU
9 5 4 3 DEPT121 DEAN
( 9 row(s) affected)
-- 二,CET with
-- 递归
with dept_cet(id,lv)
as
( select id, 0
from department where top_dept = ' Y '
union all
select a.id ,lv + 1
from department as a join
dept_cet as b
on a.superior_dept = b.id)
select * from dept_cet