SQLserver, Oracle 限制层数 递归查询和反向查询父记录

标签: sqlserver oracle 限制 | 发表时间:2013-11-11 17:20 | 作者:yanzelai
出处:http://www.iteye.com

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



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [sqlserver oracle 限制] 推荐:

SQLserver, Oracle 限制层数 递归查询和反向查询父记录

- - Oracle - 数据库 - ITeye博客
以前使用Oracle,觉得它的递归查询很好用,就研究了一下SqlServer,发现它也支持在Sql里递归查询. SqlServer2008版本的Sql如下:. 比如一个表,有id和pId字段,id是主键,pid表示它的上级节点,表结构和数据:. --下面的Sql是查询出1结点的所有子结点. select * from my1 --结果包含1这条记录,如果不想包含,可以在最后加上:where id <> 1.

oracle、mysql和sqlserver分页

- - Oracle - 数据库 - ITeye博客
sql server row number分页:. mysql limit分页:. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

Oracle、Db2、SqlServer、MySQL 数据库插入当前系统时间

- - CSDN博客推荐文章
例如有表table,table 中有两个字段:name 、makedate. 插入系统时间应为sysdate:. insert into table (name,makedate) values('测试',sysdate);. 插入系统时间应为current timestamp并且makedate数据类型为timestamp.

能使 Oracle 索引失效的七大限制条件

- - Oracle - 数据库 - ITeye博客
Oracle 索引的目标是避免全表扫描,提高查询效率,但有些时候却适得其反. 例如一张表中有上百万条数据,对某个字段加了索引,但是查询时性能并没有什么提高,这可能是 oracle 索引失效造成的. oracle 索引有一些限制条件,如果你违反了这些索引限制条件,那么即使你已经加了索引,oracle还是会执行一次全表扫描,查询的性能不会比不加索引有所提高,反而可能由于数据库维护索引的系统开销造成性能更差.

Oracle Java SE 8 发行版更新:限制商业或生产用途

- - 开源中国社区最新新闻
Oracle Java SE 8 发行版更新. Oracle Java SE 8 的公开更新仍面向单独的个人使用提供,至少持续至 2020 年底. 2019 年 1 月以后发布的 Oracle Java SE 8 公开更新将不向没有商用许可证的业务、商用或生产用途提供. 如果您是使用者,将 Java 用于单独的个人用途,则至少在 2020 年底之前,您对 Oracle Java SE 8 更新仍具有与现在相同的访问权限.

SQLServer 镜像功能完全实现

- Bloger - 博客园-首页原创精华区
折腾SQLServer 镜像搞了一天,终于有点成果,现在分享出来,之前按网上做的出了很多问题. 现在尽量把所遇到的问题都分享出来. 在域环境下我没配置成果,也许是域用户的原因,因为我在生产环境下搞的,更改域用户需要重启SQLServer ,所以这个方法放弃了,只能用证书形式. 主机:192.168.10.2  (代号A).

SQLServer索引的四个高级特性

- - CSDN博客数据库推荐文章
SQLServer索引的四个高级特性. 一、Index Building Filter(索引创建时过滤).         有一些索引非常低效的,比如经常查询状态为进行中的订单,订单有99%的状态是完成,1%是进行中 ,因此我们在订单状态字段上建了一个索引,性能是提高了,但是感觉索引中保存了99%的完成状态数据是永远不会查询到的,很浪费空间.

sqlserver数据库大型应用解决方案总结

- - 博客园_首页
随着互联网应用的广泛普及,海量数据的存储和访问成为了系统设计的瓶颈问题. 对于一个大型的互联网应用,每天百万级甚至上亿的PV无疑对数据库造成了相当高的负载. 对于系统的稳定性和扩展性造成了极大的问题. 负载均衡集群是由一组相互独立的计算机系统构成,通过常规网络或专用网络进行连接,由路由器衔接在一起,各节点相互协作、共同负载、均衡压力,对客户端来说,整个群集可以视为一台具有超高性能的独立服务器.

SqlServer索引的原理与应用 - 张龙豪

- - 博客园_首页
索引的用途:我们对数据查询及处理速度已成为衡量应用系统成败的标准,而采用索引来加快数据处理速度通常是最普遍采用的优化方法. 索引是什么:数据库中的索引类似于一本书的目录,在一本书中使用目录可以快速找到你想要的信息,而不需要读完全书. 在数据库中,数据库程序使用索引可以重啊到表中的数据,而不必扫描整个表.

B-Tree索引在sqlserver和mysql中的应用

- - CSDN博客数据库推荐文章
在谈论数据库性能优化的时候,通常都会提到“索引”,但很多人其实没有真正理解索引,并没有搞清楚索引为什么能加快检索速度,以至于在实践中并不能很好的应用索引. 事实上,索引可以说是最廉价而且十分有效一种优化手段,一般而言,设计优良的索引对查询性能优化确实能起到立竿见影的效果. 相信很多读者,都了解和使用过索引,可能也看过或者听过”新华字典“、”图书馆“之类比较通俗描述,但是对索引的存储结构和本质任然还比较迷茫.