WITH a AS(
SELECT 'a1' col1,'AA' col2 FROM dual
UNION ALL
SELECT 'a1' col1,'BB' col2 FROM dual
UNION ALL
SELECT 'b1' col1,'AA' col2 FROM dual
UNION ALL
SELECT 'b1' col1,'BB' col2 FROM dual
UNION ALL
SELECT 'c1' col1,'BB' col2 FROM dual
UNION ALL
SELECT 'c1' col1,'AA' col2 FROM dual
UNION ALL
SELECT 'c1' col1,'BB' col2 FROM dual
),
b AS(
SELECT 'a1' col1,'AAA' col2 FROM dual
UNION ALL
SELECT 'a1' col1,'BBB' col2 FROM dual
UNION ALL
SELECT 'b1' col1,'AAA' col2 FROM dual
UNION ALL
SELECT 'b1' col1,'BBB' col2 FROM dual
UNION ALL
SELECT 'c1' col1,'BBB' col2 FROM dual
)
SELECT col1, col2, col3
FROM
(
SELECT a.col1, a.col2, b.col2 col3,
row_number() over(PARTITION BY a.col1 ORDER BY NULL) rn,
COUNT(*) over(PARTITION BY a.col1) ct
FROM a, b where a.col1 = b.col1
)
WHERE rn = ct;
|