SQL查找列中的组合

Question

有一个包含两列的表格，PART_NUMBER 和 PART_TYPE。一个类型可以有很多部分。要求是使用 sql 过程找到部件号的唯一组合。我们使用 Oracle 作为我们的数据库。示例数据如下:

PART_NO       PART_TYPE 

Part1            10

Part2            10

Part3            10 

Part4            20

Part5            30

Part6            30

Part7            40

我希望输出如下。

1: Part1 Part4 Part5 Part7

2: Part1 Part4 Part6 Part7

3: Part2 Part4 Part5 Part7 

4: Part2 Part4 Part6 Part7

5: Part3 Part4 Part5 Part7

6: Part3 Part4 Part6 Part7

即)每种类型的每个部分形成一个组合。

请以算法或 sql 过程的形式对此进行说明。

Answer 1

使用分层查询:

SQL Fiddle

Oracle 11g R2 架构设置:

CREATE TABLE parts AS
          SELECT 'Part1' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part2' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part3' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part4' AS PART_NO, 20 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part5' AS PART_NO, 30 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part6' AS PART_NO, 30 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part7' AS PART_NO, 40 AS PART_TYPE FROM DUAL;

查询 1:

WITH combinations AS (
  SELECT SYS_CONNECT_BY_PATH( PART_NO, ' ' ) AS parts,
         CONNECT_BY_ISLEAF AS leaf
  FROM   parts
  START WITH PART_TYPE = 10
  CONNECT BY PRIOR PART_TYPE + 10 = PART_TYPE
)
SELECT ROWNUM || ':' || parts AS output
FROM   combinations
WHERE  leaf = 1

Results :

|                     OUTPUT |
|----------------------------|
| 1: Part1 Part4 Part5 Part7 |
| 2: Part1 Part4 Part6 Part7 |
| 3: Part2 Part4 Part5 Part7 |
| 4: Part2 Part4 Part6 Part7 |
| 5: Part3 Part4 Part5 Part7 |
| 6: Part3 Part4 Part6 Part7 |

编辑 Rob van Wijk:由于 connect_by_isleaf 是在 connect by 之后计算的，所以一个稍微简单的查询是:

SQL>  select rownum || ':' || sys_connect_by_path(part_no, ' ') as parts
  2     from parts
  3    where connect_by_isleaf = 1
  4  connect by prior part_type + 10 = part_type
  5    start with part_type = 10
  6  /

PARTS
---------------------------------------------------------------------------------------
1: Part1 Part4 Part5 Part7
2: Part1 Part4 Part6 Part7
3: Part2 Part4 Part5 Part7
4: Part2 Part4 Part6 Part7
5: Part3 Part4 Part5 Part7
6: Part3 Part4 Part6 Part7

6 rows selected.

编辑 - 非增量 PART_TYPE

SQL Fiddle

查询 3:

WITH part_types AS (
  SELECT DISTINCT PART_TYPE
  FROM   parts
),
ordered_part_types AS (
  SELECT PART_TYPE,
         LEAD( PART_TYPE ) OVER ( ORDER BY PART_TYPE ) AS NEXT_PART_TYPE
  FROM   part_types
)
SELECT ROWNUM || ':' || SYS_CONNECT_BY_PATH( PART_NO, ' ' ) AS parts
FROM   parts p
       INNER JOIN
       ordered_part_types t
       ON ( p.PART_TYPE = t.PART_TYPE )
WHERE  CONNECT_BY_ISLEAF = 1
START WITH p.PART_TYPE = ( SELECT MIN( PART_TYPE ) FROM parts )
CONNECT BY PRIOR NEXT_PART_TYPE = p.PART_TYPE

Results :

|                      PARTS |
|----------------------------|
| 1: Part3 Part4 Part6 Part7 |
| 2: Part3 Part4 Part5 Part7 |
| 3: Part2 Part4 Part6 Part7 |
| 4: Part2 Part4 Part5 Part7 |
| 5: Part1 Part4 Part6 Part7 |
| 6: Part1 Part4 Part5 Part7 |