现有一张部门表e_department:
departmentid departmentname parentid
CREATETABLE e_department(departmentid INT,departmentname VARCHAR(50),parentid INT)
row_number,rank使用:
select row_number() over(order by parentid) as rownumber,--还可以用partition来分类 例如性别
rank() over(order by parentid) rank_number,
dense_rank() over(order by parentid) as denserank_bymber,
ntile(5) over(order by parentid) as ntilenum,--强制分段
parentid as deptid
from e_department where parentid is not null
结果:
一目了然了吧。
row_number ,rank,dese-rank 在Oracle中同样如此。