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