时间戳

首页 > 解决方案 > 具有所有组合的 Oracle 返回树

sql - 具有所有组合的 Oracle 返回树

问题描述

我需要一个返回组内所有女性的查询。

示例数据表（Oracle 11g）：

with t as (
  select 'K' as GROUP_NAME, 1 as ELEMENT_ID from dual
  union
  select 'K' as GROUP_NAME, 2 as ELEMENT_ID from dual
  union
  select 'K' as GROUP_NAME, 3 as ELEMENT_ID from dual
  union
  select 'L' as GROUP_NAME, 1 as ELEMENT_ID from dual
  union
  select 'L' as GROUP_NAME, 2 as ELEMENT_ID from dual
  union
  select 'L' as GROUP_NAME, 3 as ELEMENT_ID from dual
  union
  select 'P' as GROUP_NAME, 1 as ELEMENT_ID from dual
  union
  select 'P' as GROUP_NAME, 2 as ELEMENT_ID from dual
  union
  select 'P' as GROUP_NAME, 3 as ELEMENT_ID from dual
)
select * from t

K   1
K   2
K   3
L   1
L   2
L   3
P   1
P   2
P   3

我需要生成一个包含所有组合的树，而不是在组中重复它们（组 K、L、P）

一棵树不能包含一组树，即不能有这样一个分支的树：K1->L1->K2

K1
 +-- L1
   +-- P1
   +-- P2
   +-- P3
 +-- L2
   +-- P1
   +-- P2
   +-- P3
 +-- L3
   +-- P1
   +-- P2
   +-- P3
K2
 +-- L1
   +-- P1
   +-- P2
   +-- P3
 +-- L2
   +-- P1
   +-- P2
   +-- P3
 +-- L3
   +-- P1
   +-- P2
   +-- P3
K3
 +-- L1
   +-- P1
   +-- P2
   +-- P3
 +-- L2
   +-- P1
   +-- P2
   +-- P3
 +-- L3
   +-- P1
   +-- P2
   +-- P3
L1
  +-- K1
    +-- P1
    +-- P2
    +-- P3
  +-- K2
   ....

标签： sqloracletree

解决方案

我讨厌回答这样的问题，因为 SO不是代码编写服务。

但这是一个简单的问题，我认为解决这个问题需要付出更多的努力，而不是仅仅回答，特别是因为您确实提供了测试数据和预期结果。

SELECT ltrim(sys_connect_by_path(group_name || element_id,'-'),'-') FROM t
START WITH t.group_name = 'K'
CONNECT BY ( t.group_name = 'L' AND PRIOR t.group_name = 'K' ) OR ( t.group_name = 'P' 
AND PRIOR t.group_name = 'L');

+-----------+
| TREE_PATH |
+-----------+
| K1        |
| K1-L1     |
| K1-L1-P1  |
| K1-L1-P2  |
| K1-L1-P3  |
| K1-L2     |
| K1-L2-P1  |
| K1-L2-P2  |
| K1-L2-P3  |
| K1-L3     |
| K1-L3-P1  |
| K1-L3-P2  |
| K1-L3-P3  |
| K2        |
| K2-L1     |
| K2-L1-P1  |
| K2-L1-P2  |
| K2-L1-P3  |
| K2-L2     |
| K2-L2-P1  |
| K2-L2-P2  |
| K2-L2-P3  |
| K2-L3     |
| K2-L3-P1  |
| K2-L3-P2  |
| K2-L3-P3  |
| K3        |
| K3-L1     |
| K3-L1-P1  |
| K3-L1-P2  |
| K3-L1-P3  |
| K3-L2     |
| K3-L2-P1  |
| K3-L2-P2  |
| K3-L2-P3  |
| K3-L3     |
| K3-L3-P1  |
| K3-L3-P2  |
| K3-L3-P3  |
+-----------+

如果您不想要一棵树而只想要所有组合，那将是“交叉连接”。像这样：

select * from t k CROSS JOIN t l CROSS JOIN t p
where k.group_name = 'K'
AND l.group_name = 'L'
and p.group_name = 'P'

+------------+------------+--------------+--------------+--------------+--------------+
| GROUP_NAME | ELEMENT_ID | GROUP_NAME_1 | ELEMENT_ID_1 | GROUP_NAME_2 | ELEMENT_ID_2 |
+------------+------------+--------------+--------------+--------------+--------------+
| K          |          1 | L            |            1 | P            |            1 |
| K          |          1 | L            |            1 | P            |            2 |
| K          |          1 | L            |            1 | P            |            3 |
| K          |          1 | L            |            2 | P            |            1 |
| K          |          1 | L            |            2 | P            |            2 |
| K          |          1 | L            |            2 | P            |            3 |
| K          |          1 | L            |            3 | P            |            1 |
| K          |          1 | L            |            3 | P            |            2 |
| K          |          1 | L            |            3 | P            |            3 |
| K          |          2 | L            |            1 | P            |            1 |
| K          |          2 | L            |            1 | P            |            2 |
| K          |          2 | L            |            1 | P            |            3 |
| K          |          2 | L            |            2 | P            |            1 |
| K          |          2 | L            |            2 | P            |            2 |
| K          |          2 | L            |            2 | P            |            3 |
| K          |          2 | L            |            3 | P            |            1 |
| K          |          2 | L            |            3 | P            |            2 |
| K          |          2 | L            |            3 | P            |            3 |
| K          |          3 | L            |            1 | P            |            1 |
| K          |          3 | L            |            1 | P            |            2 |
| K          |          3 | L            |            1 | P            |            3 |
| K          |          3 | L            |            2 | P            |            1 |
| K          |          3 | L            |            2 | P            |            2 |
| K          |          3 | L            |            2 | P            |            3 |
| K          |          3 | L            |            3 | P            |            1 |
| K          |          3 | L            |            3 | P            |            2 |
| K          |          3 | L            |            3 | P            |            3 |
+------------+------------+--------------+--------------+--------------+--------------+

推荐阅读