SqlServer with 及 OVER语句整理

/*
select 'ABCDABCDAB' as cp_no ,'ABCDABCDAB' as cp_name,'ABCDABCDAB' as cp_top into sy_company
;
insert into sy_company(cp_no,cp_name,cp_top) 
select '01','总公司',''
union select '02','02公司',''
union select '03','03公司',''
union  select '011','11公司','01'
union  select '012','12公司','01'
union  select '21','21公司','02'
union  select '22','22公司','02'
union  select '0111','0111公司','011'
union  select '0112','0112公司','011'
;
delete sy_company where cp_no = 'ABCDABCDAB'
;
select * from sy_company;
*/
--查找条件节点及其所有上级节点带级别
with cte_parent(cp_no,cp_name,cp_top,level,gro)
as
(
select cp_no,cp_name,cp_top,0 as level,cp_no as gro from sy_company where cp_no in('0112', '0111')--列出子节点查询条件 
union all
select a.cp_no,a.cp_name,a.cp_top,b.level+1,b.gro from sy_company a inner join cte_parent b--执行递归，这里就要理解下了 
on a.cp_no=b.cp_top
)
select * from cte_parent order by gro,level;--递归次数超过100会停止，上面需要增加查询条件

--查找条件节点及其所有下级节点带级别
with cte_child(cp_no,cp_name,cp_top,level,gro)
as
(
select cp_no,cp_name,cp_top,0 as level,cp_no as gro from sy_company where cp_no = '01'--列出父节点查询条件 
union all
select a.cp_no,a.cp_name,a.cp_top,b.level+1,b.gro from sy_company a
inner join cte_child b on ( a.cp_top=b.cp_no)
)
select * from cte_child order by gro,level

-----------------------------------------------------

----分出上下级后，进行分组排名排序 ----获取所有末节点会丢失小分支
with cte_child(cp_no,cp_name,cp_top,level,gro)
as
(
select cp_no,cp_name,cp_top,0 as level,cp_no as gro from sy_company where isnull(cp_top,'')=''--列出父节点查询条件 
union all
select a.cp_no,a.cp_name,a.cp_top,b.level+1,b.gro from sy_company a
inner join cte_child b on ( a.cp_top=b.cp_no)
)
-- select * from cte_child order by gro,level
-- select c.*,row_number() OVER(PARTITION BY gro ORDER BY level desc) AS rid from cte_child c --加行号
-- select c.*,DENSE_RANK() OVER(PARTITION BY gro ORDER BY level desc) AS rid from cte_child c --排名 并列不占名次位
 select c.*,RANK() OVER(PARTITION BY gro ORDER BY level asc) AS rid from cte_child c --排名 并列占名次位
-- select c.*,NTILE(4) OVER(PARTITION BY gro ORDER BY level asc) AS rid from cte_child c --排名 将数据分成指定组数，并为每一组生成一个序号
-- SELECT c.*,COUNT(1) OVER(PARTITION BY gro) AS rid from cte_child c --分组，获取组内数据行数
-- SELECT c.*,COUNT(1) OVER() AS rid from cte_child c --不分组，获取所有数据行数 --可和OVER(PARTITION BY gro)同时使用
-- SUM,AVG(平均),MAX,MIN 同上