sql2005语法特点(学习笔记)

--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