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语句写起来有些复杂.

 

• 如果没有那句“三十而立”，三十岁的男人正可以轻轻松松
• 专业论坛 http://www.inthirties.com
• 技术博客 http://blog.csdn.net/inthirties
• 个人站点 http://blog.inthirties.com
• Oracle Mysql技术论坛| 打造实用的Oracle Mysql技术交流园地