oracle两个递归查询,Sql语句里的递归查询 SqlServer2005和Oracle 两个版本

走丢了猫

于 2021-04-15 21:26:12 发布

阅读量81

点赞数

文章标签： oracle两个递归查询

本文介绍了如何使用SQL进行递归查询，包括查询节点的子节点和父节点，以及在Sales表中计算逐月返利金额的复杂递归计算。示例分别展示了在SQL Server 2005和Oracle数据库中的实现方式，涉及CTE（公共表表达式）和CONNECT BY子句。文章通过具体案例展示了SQL在数据处理和计算中的强大能力。

摘要由CSDN通过智能技术生成

SqlServer2005版本的Sql如下：

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

CREATE TABLE [aaa](

[id] [int] NULL,

[pid] [int] NULL,

[name] [nchar](10)

)

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

--下面的Sql是查询出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这条记录，如果不想包含，可以在最后加上:where id <> 1

--下面的Sql是查询出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结点和所有子结点的语句：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)

Oracle版本的Sql如下：

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

--下面的Sql是查询出1结点的所有子结点

SELECT * FROM aaa

START WITH id = 1

CONNECT BY pid = PRIOR id

--下面的Sql是查询出8结点的所有父结点 SELECT * FROM aaa

START WITH id = 8

CONNECT BY PRIOR pid = id

今天帮别人做了一个有点意思的sql，也是用递归实现，具体如下：假设有个销售表如下： CREATE TABLE [tb]( [qj] [int] NULL, -- 月份，本测试假设从1月份开始，并且数据都是连续的月份，中间没有隔断 [je] [int] NULL, -- 本月销售实际金额 [rwe] [int] NULL, -- 本月销售任务额 [fld] [float] NULL -- 本月金额大于任务额时的返利点，返利额为je*fld ) ON [PRIMARY] 现在要求计算每个月的返利金额，规则如下： 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