草庐IT

Oracle 分层查询

codeneng 2023-03-28 原文

Oracle Hierarchical Queries

我正在努力处理具有以下要求的查询：

表A

1
2
3
4
5
ID Name Key

1  A1   Key1
2  A2   Key2
3  A3   Key3

表B

1
2
3
4
5
6
7
8
ID A_ID NAME CONTAINER_A_ID
1  1    B1   NULL
2  1    B2   NULL
3  1    B3   2
4  2    B4   NULL
5  2    B5   NULL
6  3    B6   NULL
7  3    B7   NULL

表 A 中的 Key 列是唯一的

表B中的A_ID列是表A的外键

表 B 中的 CONTAINER_A_ID 列表示表 B 中的行可以是
容器，它包含由 CONTAINER_A_ID 值指示的其他数据行。

下面是例子：

输入参数为表A键列值，假设A.Key = \\'key1\\'，根据上述样本数据得出的结果为：

1
2
3
4
5
6
A.ID  A.NAME   A.KEY  B.ID  B.A_ID  B.NAME  B.CONTAINER_A_ID
1       A1     KEY1   1     1       B1      NULL
1       A1     KEY1   2     1       B2      NULL
1       A1     KEY1   3     1       B3      2
2       A2     KEY2   4     2       B4      NULL
2       A2     KEY2   5     2       B5      NULL

如果输入参数是A.Key = \\'key2\\'，则结果为：

1
2
3
A.ID  A.NAME   A.KEY  B.ID  B.A_ID  B.NAME  B.CONTAINER_A_ID
2       A2     KEY2   4     2       B4      NULL
2       A2     KEY2   5     2       B5      NULL

谢谢

  • 如果您添加 A.Key = 'key1' 作为条件，则将永远不会返回最后两个注册表！那是你真正需要的吗？
  • 是的，它是。我将在我的帖子中提供更多示例，以使我的问题更清楚，谢谢。
  • 我想我刚刚明白了。过滤器中的注册表和过滤结果中的子项。我想这会有点难。我不会尝试提供答案，因为我这里没有预言机来测试它。使用 CTE 的东西也许应该解决它。

这是用于分层查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
with TableA as
(
    select 1 id, 'A1' Name, 'Key1' key from dual union all
    select 2, 'A2', 'Key2'   from dual union all
    select 3, 'A3', 'Key3'   from dual
)
, tableb as
(
    select 1 id, 1 a_id, 'B1' name , null CONTAINER_A_ID from dual union all
    select 2 , 1 , 'B2'  , null from dual union all
    select 3 , 1 , 'B3'  , 2 from dual union all
    select 4 , 2 , 'B4'  , null from dual union all
    select 5 , 2 , 'B5'  , null from dual union all
    select 6 , 3 , 'B6'  , null from dual union all
    select 7 , 3 , 'B7'  , null from dual

)
select
    a.id, a.name, a.key, b.id, b.a_id, b.name, b.container_a_id
from
    tableb b
left join
    tablea a
on
    a.id = b.a_id
start with
    A.Key = 'Key1'
connect by
    prior b.container_a_id = b.a_id;

如果您需要订购，请在末尾添加 order by a.id, b.id,a.name,...;。

这是在 Oracle 11g 上。

如果您专门寻找 CONNECT BY，我还不知道。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
drop table t1; drop table t2;
create table t1 (id int primary key, name char(5), key char(5));
create table t2 (id int primary key, a_id int, name char(5) , container int);

insert into t1 values (1, 'A1', 'K1');
insert into t1 values (2, 'A2', 'K2');
insert into t1 values (3, 'A3', 'K3');

insert into t2 values (1, 1, 'B1', null);
insert into t2 values (2, 1, 'B2', null);
insert into t2 values (3, 1, 'B3', 2);
insert into t2 values (4, 2, 'B4', null);
insert into t2 values (5, 2, 'B5', null);
insert into t2 values (6, 3, 'B6', null);
insert into t2 values (7, 3, 'B7', null);

with t(id, name, key, bid, aid, bname, con) as (
    select a.id, a.name, a.key, b.id, b.a_id, b.name, b.container
    from    t1 a
            inner join
            t2 b
            on a.id = b.a_id
            where a.key = 'K1'
    union all
           select a.id, a.name, a.key, b.id, b.a_id, b.name, b.container
           from  t t
           inner join
           t1 a
           on a.id = t.con
           inner join
           t2 b
           on a.id = b.a_id
) select * from t;

编辑：回应豪尔赫的评论

1
insert into t2 values (4, 2, 'B4', 3);

  • 那会从孩子那里解决孩子吗？我的意思是如果 insert into t2 values (4, 2, 'B4', 3); 怎么办？您的查询是否有效？
  • @Jorge 我相信确实如此-见上文。如果您发现任何问题，请告诉我。
  • 伟大的。 1 给你 :)

CTE 可以在 11g Oracle 中使用。我刚刚看到豪尔赫在我面前。如果您只是在 CTE 中使用 tableB 然后加入 CTE 以获取所有字段，则更容易看到递归是如何工作的，像这样

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
with
recurse (a_id, b_id, parent_id)
as
  (select a_id, id, container_a_id as parent_id
  from tableB
  WHERE A_ID = 1 -- Put your parameter here
  union all
  select b.a_id, b.id, b.container_a_id
  from recurse r, tableB b
  where b.a_id = r.parent_id
  )
select r.a_id, a.name, a.key, b.id, b.a_id, b.name, b.container_a_id
from recurse r, tableA a, tableB b
where r.a_id = a.id and r.b_id = b.id
;

这得到了相同的结果，但是虽然您必须使用 a_id 而不是 a_key 作为条件，但理解递归要容易一些。

所以，把这个留在这里，以防它帮助人们了解一些关于 CTE 的知识。

Oracle分层nbspsectionbroracle

有关Oracle 分层查询的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  3. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  4. ruby-on-rails - solr 清理查询 - 2

    我在Rails上使用带有ruby​​的solr。一切正常，我只需要知道是否有任何现有代码来清理用户输入，比如以?开头的查询。或* 最佳答案 我不知道执行此操作的任何代码，但理论上可以通过查看parsingcodeinLucene来完成并搜索thrownewParseException(只有16个匹配!)。在实践中，我认为您最好只捕获代码中的任何solr异常并显示“无效查询”消息或类似信息。编辑:这里有几个“sanitizer”:http://pivotallabs.com/users/zach/blog/articles/937-s

  5. ruby-on-rails - Rails 3 在一个查询中包含多个表 - 2

    我正在为锦标赛开发一个Rails应用程序。我在这个查询中使用了三个模型:classPlayertruehas_and_belongs_to_many:tournamentsclassTournament:destroyclassPlayerMatch"Player",:foreign_key=>"player_one"belongs_to:player_two,:class_name=>"Player",:foreign_key=>"player_two"在tournaments_controller的显示操作中，我调用以下查询:Tournament.where(:id=>params

  6. ruby-on-rails - Sunspot:如何对具有不同值的多个字段进行全文查询？ - 2

    我想用sunspot重现以下原始solr查询q=exact_term_text:fooORterm_textv:foo*ORalternate_text:bar*但我无法通过标准的太阳黑子界面理解这是否可能以及如何实现，因为看起来:fulltext方法似乎不接受多个文本/搜索字段参数我不知道将什么参数作为第一个参数传递给fulltext，就好像我通过了"foo"或"bar"结果不匹配如果我传递一个空参数，我得到一个q=*:*范围过滤器(例如with(:term).starting_with('foo*')(顾名思义)作为过滤器查询应用，因此不参与评分。似乎可以手动编写字符串(或者可能使

  7. ruby-on-rails - 在不重新查询数据库的情况下重新排序 Rails 中的事件记录？ - 2

    例如，假设我有一个名为Products的模型，并且在ProductsController中，我有以下代码用于product_listView以显示已排序的产品。@products=Product.order(params[:order_by])让我们想象一下，在product_listView中，用户可以使用下拉菜单按价格、评级、重量等进行排序。数据库中的产品不会经常更改。我很难理解的是，每次用户选择新的order_by过滤器时，rails是否必须查询，或者rails是否能够以某种方式缓存事件记录以在服务器端重新排序？有没有一种方法可以编写它，以便在用户排序时rails不会重新查询结果

  8. ruby-on-rails - 带句点(或句号)的 Rails 查询字符串。 - 2

    我目前正在尝试了解RoR。我将两个字符串传递到我的Controller中。一个是随机的十六进制字符串，另一个是电子邮件。该项目用于对数据库进行简单的电子邮件验证。我遇到的问题是当我输入如下内容来测试我的页面时:http://signup.testsite.local/confirm/da2fdbb49cf32c6848b0aba0f80fb78c/bob.villa@gmailcom我在:email的参数散列中得到的全部是'bob'。我在gmail和com之间留下了.，因为那样会导致匹配根本不起作用。我的路由匹配如下:match"confirm/:code/:email"=>"conf

  9. ruby - 如何将编码的查询值添加到 URL？ - 2

    我正在寻找一种方便实用的方法来将编码值添加到Ruby中的URL查询字符串。目前，我有:require'open-uri'u=URI::HTTP.new("http",nil,"mydomain.example",nil,nil,"/tv",nil,"show="+URI::encode("Rosie&Jim"),nil)pu.to_s#=>"http://mydomain.example/tv?show=Rosie%20&%20Jim"这不是我要找的，因为我需要得到“http://mydomain.example/tv?show=Rosie%20%26%20Jim”，这样show=值就

  10. 如何判断oracle是否启动及启动oracle数据库 - 2

    plsql连接Oracle超时，完犊子了肯定是服务器断电了。得马上检查Oracle服务器状态1、检查数据库是否启动su-oracle切换到Oracle用户，输入sqlplus/assysdba显示连接状态。如果末尾显示的状态是Connectedtoanidleinstance.证明未启动2、启动数据库startup启动数据库，末尾出现Databaseopened说明数据库启动成功3、查看数据库监听是否正常先quit;断开Oracle连接，使用lsnrctlstatus查看监听状态，如果出现TNS-开头的Nolistener、Connectionrefused等错误，说明监听未启动4、启动数据库

随机推荐