如何在Oracle中使用联接减少查询深度

rn0zuynd 于 2023-10-16 发布在 Oracle

关注(0)|答案(3)|浏览(95)

假设有这样的table。
1.表t0
| 用户标识|名称|
| --|--|
| 100 |'a'|
| 101 |'B'|
| 102 |'C'|
1.表t1
| ID|用户标识|
| --|--|
| 1 | 100 |
| 8 | 101 |
| 9 | 102 |
1.表t2
| ID|标志|
| --|--|
| 1 | 0 |
| 8 | 0 |
| 9 | 1 |
1.表t3
| ID|基ID|标志|
| --|--|--|
| 2 | 1 | 1 |
| 5 | 1 | 0 |
| 5 | 2 | 0 |
1.表t4
| ID|基ID|标志|
| --|--|--|
| 3 | 2 | 1 |
| 4 | 2 | 1 |
| 6 | 5 | 0 |
| 7 | 5 | 1 |
我想知道所有标志为1的数据的名称。t3是t2的子表，t4是t3的子表。如果超级表的数据的标志是1，则子表的标志也是1，但反之则不然。
在本例中，我这样编写查询语句。

select t1.id, t0.name from t0 join t1 on t0.user_id = t1.id where t1.id in 
  (select id from t2 where flag = 1 
   union 
   select base_id from t3 where flag = 1 or id in
     (select base_id from t4 where flag = 1)
  )
;

我认为我的查询不够好，我认为有一个更好的方法来完成这项任务，比如使用join等来减少深度。但我不知道该怎么做……
帮帮我...还有更好的办法吗？

oracle

来源：https://stackoverflow.com/questions/77086647/how-can-i-reduce-query-depths-using-join-in-oracle

3条答案

按热度按时间

fzsnzjdm1#

对我来说，它看起来像外部连接（带有检查标志值的条件）。
样本数据：

SQL> with
  2  t0 (user_id, name) as
  3    (select 100, 'a' from dual union all
  4     select 101, 'b' from dual union all
  5     select 102, 'c' from dual
  6    ),
  7  t1 (id, user_id) as
  8    (select 1, 100 from dual union all
  9     select 8, 101 from dual union all
 10     select 9, 102 from dual
 11    ),
 12  t2 (id, flag) as
 13    (select 1, 0 from dual union all
 14     select 8, 0 from dual union all
 15     select 9, 1 from dual
 16    ),
 17  t3 (id, base_id, flag) as
 18    (select 2, 1, 1 from dual union all
 19     select 5, 1, 0 from dual union all
 20     select 5, 2, 0 from dual
 21    ),
 22  t4 (id, base_id, flag) as
 23    (select 3, 2, 1 from dual union all
 24     select 4, 2, 1 from dual union all
 25     select 6, 5, 0 from dual union all
 26     select 7, 5, 1 from dual
 27    )

查询方式：

28  select distinct t0.name
 29  from t0
 30    left join t1 on t1.user_id = t0.user_id
 31    left join t2 on t2.id = t1.id
 32    left join t3 on t3.base_id = t2.id
 33    left join t4 on t4.base_id = t3.id
 34  where t2.flag = 1
 35     or t3.flag = 1
 36     or t4.flag = 1;

NAME
-----
a
c

SQL>

赞(0）回复(0）举报 2023-10-16

iszxjhcz2#

你可以这样重写你的查询

with
    t0 (user_id, name) as
    (select 100, 'a' from dual union all
       select 101, 'b' from dual union all
       select 102, 'c' from dual
      ),
    t1 (id, user_id) as
      (select 1, 100 from dual union all
       select 8, 101 from dual union all
      select 9, 102 from dual
     ),
   t2 (id, flag) as
     (select 1, 0 from dual union all
      select 8, 0 from dual union all
      select 9, 1 from dual
     ),
   t3 (id, base_id, flag) as
     (select 2, 1, 1 from dual union all
      select 5, 1, 0 from dual union all
     select 5, 2, 0 from dual
     ),
   t4 (id, base_id, flag) as
     (select 3, 2, 1 from dual union all
      select 4, 2, 1 from dual union all
      select 6, 5, 0 from dual union all
      select 7, 5, 1 from dual
     )
select t1.id, t0.name 
   from t0 join t1 on t0.user_id = t1.user_id
   JOIN   (select id from t2 where flag = 1 
   union 
   select t3.base_id from t3 
   JOIN t4 ON t3.id = t4.base_id  where t3.flag = 1 OR t4.flag = 1) t2a
   ON t1.id = t2a.id

| ID|名称|
| --|--|
| 9 |C|
| 1 |一|
fiddle
但是如果表中有很多行，

赞(0）回复(0）举报 2023-10-16

zlwx9yxi3#

你可以使用EXISTS来重写它：

SELECT t1.id,
       t0.name
FROM   t0
       INNER JOIN t1
       ON (t0.user_id = t1.id)
WHERE  EXISTS(
         SELECT 1
         FROM   t2
         WHERE  flag = 1 
         AND    t1.id = t2.id
       )
OR     EXISTS(
         SELECT 1
         FROM   t3
         WHERE  t3.base_id = t1.id
         AND    (  flag = 1
                OR id IN (SELECT base_id FROM t4 WHERE flag = 1)
                )
       );

或：

SELECT t1.id,
       t0.name
FROM   t0
       INNER JOIN t1
       ON (t0.user_id = t1.id)
WHERE  EXISTS(
         SELECT 1
         FROM   t2
         WHERE  flag = 1 
         AND    t1.id = t2.id
       )
OR     EXISTS(
         SELECT 1
         FROM   t3
                LEFT OUTER JOIN t4
                ON (t3.id = t4.base_id AND t4.flag = 1)
         WHERE  t3.base_id = t1.id
         AND    (t3.flag = 1 OR t4.flag = 1)
       );

赞(0）回复(0）举报 2023-10-16

我来回答

如何在Oracle中使用联接减少查询深度

3条答案

相关问题

热门标签

最新问答