您可以使用聚合和having
:
select part_no
from t
group by part_no
having min(family) <> max(family);
,
使用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
,
如果您希望零件号以及与其链接的系列和总数,则可以使用以下分析功能:
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,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>
干杯!
本文链接:https://www.f2er.com/3156418.html