Sql递归原理

谁践踏了优雅 2022-05-22 07:48 270阅读 0赞

#### Sql递归原理 ####

递归最少包括两个查询（也被称为成员）。第一个查询为定点成员，定点成员只是一个返回有效表的查询，用于递归的基础或定位点。   
第二个查询被称为递归成员，使该查询成为递归成员的是对递归引用时触发（递归表.id与该表的pid）。在逻辑上，可以将其理解为是前一个查询语句的子集。   
递归查询没有显式的递归终止条件，只有当第二个递归查询返回空结果集或是超出了递归次数的最大限制时才停止递归。递归次数上限的方法是使用MAXRECURION。

#### 1、SqlServer2005版本的Sql如下： ####

比如一个表，有id和pId字段，id是主键，pid表示它的上级节点，表结构和数据：

CREATE TABLE [aaa]( [id] [int] NULL, [pid] [int] NULL, [name] [nchar](10) ) GO INSERT INTO aaa VALUES(1,0,'a') INSERT INTO aaa VALUES(2,0,'b') INSERT INTO aaa VALUES(3,1,'c') INSERT INTO aaa VALUES(4,1,'d') INSERT INTO aaa VALUES(5,2,'e') INSERT INTO aaa VALUES(6,3,'f') INSERT INTO aaa VALUES(7,3,'g') INSERT INTO aaa VALUES(8,4,'h') GO

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15

查询出1结点的所有子结点

--
    --结果包含1这条记录，如果不想包含，可以在最后加上:where id <> 1
    with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) select * from my1

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7

查询出8结点的所有父结点

with my1 as(select * from aaa where id = 8 union all select aaa.* from my1, aaa where my1.pid = aaa.id ) select * from my1;

*  1
 *  2
 *  3
 *  4

递归删除1结点和所有子结点的语句：

with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) delete from aaa where exists (select id from my1 where my1.id = aaa.id)

*  1
 *  2
 *  3
 *  4

#### 2、Sql递归案例： ####

假设有个销售表如下：

CREATE TABLE [tb]( [qj] [int] NULL, -- 月份，本测试假设从1月份开始，并且数据都是连续的月份，中间没有隔断 [je] [int] NULL, -- 本月销售实际金额 [rwe] [int] NULL, -- 本月销售任务额 [fld] [float] NULL -- 本月金额大于任务额时的返利点，返利额为je*fld ) ON [PRIMARY]

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

现在要求计算每个月的返利金额，规则如下：   
1月份销售金额大于任务额 返利额=金额\*返利点   
2月份销售金额大于任务额 返利额=（金额-1月份返利额）\*返利点   
3月份销售金额大于任务额 返利额=（金额-1,2月份返利额）\*返利点   
以后月份依次类推，销售额小于任务额时，返利为0   
具体的Sql如下：

WITH my1 AS (
       SELECT *,CASE WHEN je > rwe THEN (je * fld) ELSE 0 END fle, CAST(0 AS FLOAT) tmp FROM tb WHERE qj = 1 UNION ALL SELECT tb.*,CASE WHEN tb.je > tb.rwe THEN (tb.je - my1.fle -my1.tmp) * tb.fld ELSE 0 END fle, my1.fle + my1.tmp tmp --用于累加前面月份的返利 FROM my1,tb WHERE tb.qj = my1.qj + 1 ) SELECT * FROM my1

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11

#### 3、表达式递归查询 ####

--由父项递归下级 
    with cte(id,parentid,text) 
    as 
    (--父项 
    select id,parentid,text from treeview where parentid = 450 
    union all 
    --递归结果集中的下级 
    select t.id,t.parentid,t.text from treeview as t 
    inner join cte as c on t.parentid = c.id 
    ) 
    select id,parentid,text from cte

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11

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

--由子级递归父项 
    with cte(id,parentid,text) 
    as 
    (--下级父项 
    select id,parentid,text from treeview where id = 450 
    union all 
    --递归结果集中的父项 
    select t.id,t.parentid,t.text from treeview as t 
    inner join cte as c on t.id = c.parentid 
    ) 
    select id,parentid,text from cte