在2列之间查找一对一/一对多关系

Question

我正在生成一个零件号及其关联族的列表。有些零件号属于多个族，其中大多数只与一个族关联。

要使用整数，有600个不同的零件，其中有750个不同的部件/族关系。所以我想做的是创建一个零件号列表，这些零件号不属于一个族。

作为一个例子，我在下面创建了一个子集的数据，然后还创建了预期的返回。

part_no       family
43d565rfd     ar94
43d000rfd     ur22
43d000rfd     e498
43d565r12     ur24
43d565rfd     ur24
43d365r56     ev69
43d365r56     as56

因此，从上面的列表中，我只想返回：

part_no
43d000rfd
43d365r56

因为他们是多个家庭的一部分。

Answer 1

您可以使用聚合和having:

select part_no
from t
group by part_no
having min(family) <> max(family);

Answer 2

使用HAVING子句筛选出属于单个族的零件。例如：

select part_no
from (select distinct part_no, family from t) x
group by part_no
having count(*) > 1

或者：

select part_no
from t
group by part_no
having count(distinct family) > 1

Answer 3

如果您想要零件号以及与之链接的族和总数，那么您可以使用以下分析函数：

SQL> WITH YOUR_TABLE(part_no, family) AS
2  (SELECT '43d565rfd',     'ar94' FROM DUAL UNION ALL
3  SELECT '43d000rfd',     'ur22' FROM DUAL UNION ALL
4  SELECT '43d000rfd',     'e498' FROM DUAL UNION ALL
5  SELECT '43d565r12',     'ur24' FROM DUAL UNION ALL
6  SELECT '43d565rfd',     'ur24' FROM DUAL UNION ALL
7  SELECT '43d365r56',     'ev69' FROM DUAL UNION ALL
8  SELECT '43d365r56',     'as56' FROM DUAL)
9  SELECT
10      PART_NO,
11      FAMILY,
12      CNT   AS TOTAL_FAMILIES
13  FROM
14      (
15          SELECT
16              PART_NO,
17              FAMILY,
18              COUNT(1) OVER(
19                  PARTITION BY PART_NO
20              ) AS CNT
21          FROM YOUR_TABLE
22      ) WHERE CNT > 1
23  ORDER BY PART_NO;
PART_NO   FAMI TOTAL_FAMILIES
--------- ---- --------------
43d000rfd ur22           2
43d000rfd e498           2
43d365r56 ev69           2
43d365r56 as56           2
43d565rfd ar94           2
43d565rfd ur24           2
6 rows selected.
SQL>

或者，如果您希望族位于逗号分隔的列表中，请使用以下内容：

SQL>
SQL> WITH YOUR_TABLE(part_no, family) AS
2  (SELECT '43d565rfd',     'ar94' FROM DUAL UNION ALL
3  SELECT '43d000rfd',     'ur22' FROM DUAL UNION ALL
4  SELECT '43d000rfd',     'e498' FROM DUAL UNION ALL
5  SELECT '43d565r12',     'ur24' FROM DUAL UNION ALL
6  SELECT '43d565rfd',     'ur24' FROM DUAL UNION ALL
7  SELECT '43d365r56',     'ev69' FROM DUAL UNION ALL
8  SELECT '43d365r56',     'as56' FROM DUAL)
9  SELECT
10      PART_NO,
11      LISTAGG(FAMILY, ',') WITHIN GROUP(
12          ORDER BY
13              1
14      ) AS FAMILIES,
15      COUNT(1) AS TOTAL_FAMILIES
16  FROM
17      YOUR_TABLE
18  GROUP BY
19      PART_NO
20  HAVING
21      COUNT(DISTINCT FAMILY) > 1
22  ORDER BY
23      PART_NO
24  ;
PART_NO   FAMILIES             TOTAL_FAMILIES
--------- -------------------- --------------
43d000rfd e498,ur22                         2
43d365r56 as56,ev69                         2
43d565rfd ar94,ur24                         2
SQL>

干杯！！