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

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

作者:​​三十而立​​

Connect by是Oracle里非常强大的递归查询的功能。 通过这个功能我们来进行类似父子关系的递归树的查询。

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

的一个网友，比较有趣的把这两者合在了一起，也就产生了这个题目。 如何用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语句写起来有些复杂.

如果没有那句“三十而立”，三十岁的男人正可以轻轻松松

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。