Connect by--PLSQL中的接力棒传递游戏

 作者: 三十而立
时间:2009年10月22日 19:27:22
请尊重原创作品。转载请保持文章完整性,并以超链接形式注明原始作者“inthirties(三十而立)”和出处”http://blog.csdn.net/inthirties/archive/2009/10/22/4711333.aspx”,深入讨论可以联系inthirties@gmail.com
Connect by是Oracle里非常强大的递归查询的功能。 通过这个功能我们来进行类似父子关系的递归树的查询。

接力棒传递呢,是我们都玩过,或者是看到过的游戏, 接力棒从一个人传递到另一个人,直到最后一个完成冲刺的人。

CSDN上的一个网友,比较有趣的把这两者合在了一起,也就产生了这个题目。 如何用PLSQL来完成这个接力棒传递的游戏了,我们一起来看看具体的要求。

本人开发中遇到个这样的问题,有点像接力棒传递的体育游戏。

简单应用,单个棒传递。
场景:跑完1号棒结束。
表结构:
A表
接力棒,当前传递人,下个接棒人
1号棒,李一,李二  //注释:表示李一为接力赛的第一个传棒人
1号棒,李二,李三
1号棒,李三,李四
1号棒,李四,李五
1号棒,李五,李六  //注释:表示李六为接力赛的最后接棒人

问题:

从李一到李五,之间总共传了几棒?SQL语句如何编写?

仔细分析一下这里的表结构,我们不难发现,这里的表结构很类似父子关系, 当当前的传递人把接力棒传递给下一个接棒人的时候,就是根据这里的下个接棒人来进行选择的,当我们从李一出来,找到李二,然后李二的下一个接棒人,是李三,在根据李三的下一个接棒人找到后面的, 所以是个典型的递归的引用,引用的递归规则就是 第一条的 “下个接棒人 ” 就是 下一条记录里的 “当前传递人”, 依次循环递归下去,直到 “李四”。 需求如此,怎么实现了。

下面就该我们的Connect by隆重登场了。 首先就提到过connect by是专门来最递归查询的,他就是利用数据库里表结构的设计的递归应用的关系,进行遍历,从而达到我们的要求。connect by后面是进行递归的条件,在这个条件句子里经常会使用到prior这个关键字, 这个关键字表示的是前一条记录, 比如 connect by prior id = parentid, 这里表明递归的条件规则是 上一条记录的id是下一条记录的parentid,这样下一条的id又是下下条的parentid, 一层层的归结下去,知道不再满足这里的connect by里的条件,表示已经到达了树的叶了。 在递归里,还有一个也经常用到 start with,这里表示的是遍历的起始的条件, 比如start with parentid = 0 or parentid is null。 这样我们可以确定来的从什么条件开始遍历, 通过start with ..... connect by的配合,我们从而可以很有针对性的拿到我们的需要的树形的数据叻。

上面了解了Oracle里递归的功能,下面我们来看看怎么来实现这个PLSQL里的接力棒传递的游戏叻

根据上面的分析,我们可以看到这个接力棒的传递表,就是一个父子引用的表,通过“下个接棒人” 传递着接力棒,我们的Connect by的接力也开始了。 前面的 “下个接棒人” 就是 下一条记录的 “当前传递人”,这条的 “下个接棒人”就是下下条的 “当前传递人”, 我们的connect by 就是应该以这两个字段来进行引用递归iude关联了。

connect by prior “下个接棒人” = & ldquo;当前传递人”

接力棒应该同一个棒传下去,不要一棒传到二棒了,那就是帮人家忙了。 所以我们的 connect by还要加上

prior "接力棒" = “接力棒”

所以这里的条件为 connect by  prior “下个接棒人” = “当前传递人”  and prior "接力棒" = “接力棒”

题中要求李一开始,所以开始递归的条件也出来了

start with “当前传递人”='李一'

题中还要求以李四结尾

所以connect by里还要加上这个条件

“当前传递人”<>'李四'

最后综合起来的 递归的sql是

start with “当前传递人”='李一' connect by “当前传递人”<>'李四' and  prior “ 下个接棒人” = “当前传递人”  and prior "接力棒" = “接力棒”

SQL> with tt as(select '1号棒' "接力棒",'李一' "当前传递人",'李二' "下个接棒人" from dual

  2  union all select '1号棒' ,'李二' ,'李三'  from dual

  3  union all select '1号棒' ,'李三' ,'李四'  from dual)

  4  select * from tt start with "当前传递人"='李一' connect by "当前传递人"<>'李四' and  prior "下个接棒人" = "当前传递人"  and prior "接力棒" = "接力棒"

  5  ;

接力棒    当前传递人      下个接棒人

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

1号棒     李一            李二

1号棒     李二            李三

1号棒     李三            李四

这里要求统计数据

所以还需要分组,和 group by

以上我们可以写成

SQL> with tt as(select '1号棒' "接力棒",'李一' "当前传递人",'李二' "下个接棒人" from dual

  2  union all select '1号棒' ,'李二' ,'李三'  from dual

  3  union all select '1号棒' ,'李三' ,'李四'  from dual)

  4  select "接力棒", count(1)+1 from (select * from tt start with "当前传递人"='李一'

  5  connect by "当前传递人"<>'李四' and  prior "下个接棒人" = "当前传递人"  and prior "接力棒" = "接力棒") group by "接力棒"

  6  ;

接力棒    COUNT(1)+1

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

1号棒              4

现在就出来我们需要的结果了, 不过有时候我们不知道最上面的 “李一”, 那么我们可以先用 not exists 来查找这些最前面的接力棒选手

用这个子查询,可以查出所有的开始的选手

select * from tt where not exists (select 1 from tt t where t.下个接棒人=tt.当前传递人 and tt."接力棒" =t."接力棒")

把这个和start with放一起。

SQL> with tt as(select '1号棒' "接力棒",'李一' "当前传递人",'李二' "下个接棒人" from dual

  2  union all select '1号棒' ,'李二' ,'李三'  from dual

  3  union all select '1号棒' ,'李三' ,'李四'  from dual)

  4  select "接力棒", count(1)+1 from (select * from tt start with not exists(select 1 from tt t where t.下个接棒人=tt.当前传递人 and tt."接力棒" =t."接力棒")

  5  connect by "当前传递人"<>'李四' and  prior "下个接棒人" = "当前传递人"  and prior "接力棒" = "接力棒") group by "接力棒"

  6  ;

接力棒    COUNT(1)+1

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

1号棒              4

个人点评:这个问题我在开发当中遇到的,实际的情景不是这样,只是类似于接力棒比赛这个游戏,不知道朋友们看懂了没有?
诈一看起来挺简单,但是用SQL语句写起来有些复杂.


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/inthirties/archive/2009/10/22/4711333.aspx

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值