两表关联后按次列最大记录数生成结果集的SQL实现问题

现有两张存在公共关联键commonkey的表，需要按以下规则取ID生成结果集：

• 当两表commonkey匹配时，统计该commonkey在两张表的记录数，取记录数更多的表的对应ID；若两表记录数相等，优先取表1的ID
• 若commonkey仅在单张表存在，无匹配关联，避免交叉连接，直接将该部分记录追加到结果集中

表1测试数据 #

SELECT '123' table1_id,'Comb A' commonkey from dual UNION
SELECT '124' table1_id,'Comb A' commonkey from dual UNION
SELECT '125' table1_id,'Comb A' commonkey from dual UNION
SELECT '126' table1_id,'Comb A' commonkey from dual UNION
SELECT '215' table1_id,'Comb B' commonkey from dual UNION
SELECT '216' table1_id,'Comb B' commonkey from dual UNION
SELECT '559' table1_id,'Random Combination 1' commonkey from dual UNION
SELECT '560' table1_id,'Random Combination 2' commonkey from dual ;

表2测试数据 #

SELECT 'abc1' table2_id,'Comb A' commonkey from dual  UNION
SELECT 'abc2' table2_id,'Comb A' commonkey from dual  UNION
SELECT 'abc3' table2_id,'Comb A' commonkey from dual  UNION
SELECT 'abc4' table2_id,'Comb A' commonkey from dual  UNION
SELECT 'xyz1' table2_id,'Comb B' commonkey from dual  UNION
SELECT 'xyz2' table2_id,'Comb B' commonkey from dual  UNION
SELECT 'xyz3' table2_id,'Comb B' commonkey from dual  UNION
SELECT 'xyz2' table2_id,'Comb B' commonkey from dual  UNION 
SELECT '416abc1' table2_id,'Random Combination 91' commonkey from dual UNION
SELECT '416abc2' table2_id,'Random Combination 92' commonkey from dual;

预期输出 #

ID        COMMONKEY         
123       Comb A            
124       Comb A            
125       Comb A            
126       Comb A            
xyz1      Comb B            
xyz2      Comb B            
xyz3      Comb B            
559       Random Combination 1          
560       Random Combination 2          
416abc1   Random Combination 91         
416abc2   Random Combination 92 

先分别统计两张表每个commonkey的记录数，再判断每个commonkey对应取哪个表的全量数据，最后合并结果即可，全程不会产生交叉连接：

WITH t1_cnt AS (
    SELECT commonkey, COUNT(*) cnt 
    FROM table1 
    GROUP BY commonkey
),
t2_cnt AS (
    SELECT commonkey, COUNT(*) cnt 
    FROM table2 
    GROUP BY commonkey
)
-- 取所有符合条件的表1数据
SELECT table1_id AS ID, commonkey
FROM table1 t1
WHERE EXISTS (
    SELECT 1 
    FROM t1_cnt c1
    LEFT JOIN t2_cnt c2 ON c1.commonkey = c2.commonkey
    WHERE c1.commonkey = t1.commonkey
    AND (c2.commonkey IS NULL OR c1.cnt >= c2.cnt)
)
UNION ALL
-- 取所有符合条件的表2数据
SELECT table2_id AS ID, commonkey
FROM table2 t2
WHERE EXISTS (
    SELECT 1 
    FROM t2_cnt c2
    LEFT JOIN t1_cnt c1 ON c2.commonkey = c1.commonkey
    WHERE c2.commonkey = t2.commonkey
    AND (c1.commonkey IS NULL OR c2.cnt > c1.cnt)
);

逻辑说明 #

• 当commonkey仅在表1存在：进入第一个SELECT分支，被正常取出
• 当commonkey仅在表2存在：进入第二个SELECT分支，被正常取出
• 当commonkey两表都存在：比较计数，表1计数≥表2就取表1所有该key的记录，否则取表2所有该key的记录，完全匹配需求规则

内容的提问来源于stack exchange，提问作者Joe_sushi39