SQL查询可解决数据库中的传递依赖项

本文介绍了SQL查询可解决数据库中的传递依赖项的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设我有一个具有以下结构的表:

Suppose I have a table with following Structure:

create table rd(r1 number,r2 number, primary key (r1,r2));

样本数据:

| R1 | R2 |
-----------
|  1 |  2 |
|  1 |  4 |
|  2 |  3 |
|  3 |  1 |
|  4 |  5 |

这意味着R1与R2双向相关.因此，如果数据库中有1,3的条目，则不会有像3,1的条目.

What it means is that R1 is related to R2 , bi-directionally. So if there is an entry in database for 1,3 there won't be an entry like 3,1.

根据以上数据:1与2,4,3直接相关.并且4也与1有关.因此，通过传递依赖性，1和5也被认为是相关的.

According to above data:1 is related to 2,4,3 directly. And 4 is related to 1 also . So via transitive dependency, 1 and 5 are also considered as related.

预期结果:

| R1 | R2 |
-----------
|  1 |  2 |
|  1 |  4 |
|  1 |  3 |
|  1 |  5 |

任何人都可以为此编写SQL查询吗?

Can anyone write a SQL query for this?

推荐答案

在运行Oracle 11g(如果恰好是版本2)时，可以使用递归公用表表达式(也称为已知方法)作为其中一种方法.作为递归子查询因子)以获得所需的结果.

As you are running Oracle 11g (and If it happens to be Release 2), as one of the methods, you can use recursive common table expression (also known as recursive sub-query factoring) to get desired result.

SQL> with rcte(r1, r2, lv, root) as(
  2    select r1
  3         , r2
  4         , 0 lv
  5         , r1
  6     from rd
  7    where r1 = 1
  8
  9    union all
 10
 11    select t.r1
 12         , t.r2
 13         , lv + 1
 14         , q.root
 15      from rd   t
 16      join rcte q
 17        on (t.r1 = q.r2)
 18  )
 19  search depth first by r1 set s
 20  cycle r1 set is_cycle to 'y' default 'n'
 21
 22  select root
 23       , r2
 24    from rcte
 25  where is_cycle = 'n'
 26    and r2 <> root
 27  ;

      ROOT         R2
---------- ----------
         1          2
         1          3
         1          4
         1          5

这篇关于SQL查询可解决数据库中的传递依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！