SQL Server over和CTE的妙用（子记录根据外键递归找出父记录，并实现层级关系码）

开窗函数是在 ISO SQL 标准中定义的。SQL Server 提供排名开窗函数和聚合开窗函数。窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

可以在单个查询中将多个排名或聚合开窗函数与单个 FROM 子句一起使用。但是，每个函数的 OVER 子句在分区和排序上可能不同。OVER 子句不能与 CHECKSUM 聚合函数结合使用。

CTE是common table expression的缩写，主要是用来实现递归的功能。

（以上详见MSDN）

/*

假设源原表中的记录为

ID   PID  

1,  NULL

2,  1,  

3,  2,

4， 1，

5， 4

假设有一些末级记录，递归将其父记录取出来，并且根据层级关系构造层次码

如:

输入是

ID PID

3   2

5   4

的记录，输出是

ID   PID     Code

1,   NULL,    1

2,    1,         1.1

3,    2,         1.1.1

4,    1,         1.2

5,    4          1.2.1

*/

IF OBJECT_ID('tempdb..#data') > 0 drop table #data

IF OBJECT_ID('tempdb..#pbs') > 0 drop table #pbs

 

create table #data

(

  ID int,

  PID int

);

 

create table #pbs

(

  ID int,

  PID int,

  Code varchar(255)

);

 

--插入原始记录

insert into #data

select 1, null

union

select 2, 1

union 

select 3, 2

union 

select 4, 1

union

select 5, 4

 

insert into #pbs

select ID, PID, row_number() over(partition by pid order by id) as Code  

from #data

 

--插入后执行

with CTE(id, pid, code) as 

(

  select id, pid, cast(code as varchar(255)) from #pbs

  where pid is null

union all

  select parent.id, parent.pid, cast((cast(child.code as varchar(255)) + '.' + cast(parent.code as varchar(255))) as varchar(255)) as code 

from #pbs parent join CTE child on parent.pid = child.id

)

select * from CTE

 

/*

结果为

1 NULL 1

2 1 1.1

4 1 1.2

5 4 1.2.1

3 2 1.1.1

*/