Sql语句里的递归查询

吞吞吐吐的 2017-10-11

Oracle SQL Sqlserver

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

 

以前使用Oracle,觉得它的递归查询很好用,就研究了一下SqlServer,发现它也支持在Sql里递归查询
举例说明:
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

--下面的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

 

SQLserver2008使用表达式递归查询

--由父项递归下级 
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

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

--由子级递归父项 
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

分类: MSMQ,ORACLE
本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/p/4374193.html,如需转载请自行联系原作者
登录 后评论
下一篇
弹性计算秉林
42人浏览
2020-07-08
相关推荐
SQL 笔记 By 华仔
2943人浏览
2016-05-09 14:42:03
T-SQL递归
1672人浏览
2016-05-19 17:50:11
PostgreSQL SQL 语言:查询
1851人浏览
2017-08-17 14:22:22
sql with as 用法
1213人浏览
2017-10-10 10:05:00
并行执行计划
704人浏览
2017-11-14 15:23:00
sql的with as高效语法
513人浏览
2014-02-23 10:23:26
sql server中的cte
557人浏览
2019-08-25 11:30:27
MySQL8.0之CTE(公用表表达式)
1899人浏览
2019-10-05 20:20:42
0
0
0
906