产品的分层SQL查询-Java 学习之路

我有产品表，其概念如下所示：

items|component
A    |A1
B1   |B4
B    |B2
B    |B1
B2   |B4
B    |B3

我需要一个显示结果的SQL查询：

items|level|component
A    |1    |A1
A1   |2    |
B    |1    |B1
B    |1    |B2
B    |1    |B3
B1   |2    |B4
B2   |2    |B4
B3   |2    |
B4   |3    |

谁能帮我这个？提前致谢 .

2 回答

你的意思是这样的吗？

drop table t1;
create table t1 (item varchar2 (10), component varchar2 (10));
insert into t1 values ('A' ,'A1');
insert into t1 values ('B' ,'B1');
insert into t1 values ('B' ,'B2');
insert into t1 values ('B' ,'B3');
insert into t1 values ('B1' ,'B4');
insert into t1 values ('B2' ,'B4');
commit;

select item, level, component from t1 connect by item=prior component;

如果您确实要将项目列表中缺少的那些组件添加到项目中，那么SQL可能如下所示：

select item, level, component 
from t1 
connect by item=prior component
union all 
select distinct component,1,null from t1 where component not in (select item from t1);

回复于 2024-05-04T08:47:13+08:00

SQL Fiddle

Oracle 11g R2 Schema Setup ：

CREATE TABLE table_names ( items, component ) AS
SELECT 'A',  'A1' FROM DUAL UNION ALL
SELECT 'B1', 'B4' FROM DUAL UNION ALL
SELECT 'B',  'B2' FROM DUAL UNION ALL
SELECT 'B',  'B1' FROM DUAL UNION ALL
SELECT 'B2', 'B4' FROM DUAL UNION ALL
SELECT 'B',  'B3' FROM DUAL;

Query 1 ：

SELECT LEVEL, t.*
FROM   (
  SELECT *
  FROM   table_names
  UNION ALL
  (
    SELECT DISTINCT
           component, NULL
    FROM   table_names
    WHERE  component NOT IN ( SELECT items FROM table_names )
  )
) t
START WITH items NOT IN ( SELECT component FROM table_names )
CONNECT BY PRIOR component = items

Results ：

| LEVEL | ITEMS | COMPONENT |
|-------|-------|-----------|
|     1 |     A |        A1 |
|     2 |    A1 |    (null) |
|     1 |     B |        B1 |
|     2 |    B1 |        B4 |
|     3 |    B4 |    (null) |
|     1 |     B |        B2 |
|     2 |    B2 |        B4 |
|     3 |    B4 |    (null) |
|     1 |     B |        B3 |
|     2 |    B3 |    (null) |

Query 2 ：

SELECT LEVEL, t.*
FROM   (
  SELECT *
  FROM   table_names
  UNION ALL
  (
    SELECT DISTINCT
           component, NULL
    FROM   table_names
    WHERE  component NOT IN ( SELECT items FROM table_names )
  )
) t
START WITH items NOT IN ( SELECT component FROM table_names )
CONNECT BY PRIOR component = items
ORDER BY CONNECT_BY_ROOT( items ), LEVEL, items

Results ：

| LEVEL | ITEMS | COMPONENT |
|-------|-------|-----------|
|     1 |     A |        A1 |
|     2 |    A1 |    (null) |
|     1 |     B |        B3 |
|     1 |     B |        B1 |
|     1 |     B |        B2 |
|     2 |    B1 |        B4 |
|     2 |    B2 |        B4 |
|     2 |    B3 |    (null) |
|     3 |    B4 |    (null) |
|     3 |    B4 |    (null) |

回复于 2024-05-04T08:47:13+08:00

产品的分层SQL查询

2 回答

相关问题