如何从存在多匹配项的两个同结构表中查询符合条件的行

解决方案 #

实现思路 #

1. 先分别统计field_1在两张表中的出现次数
2. 筛选出符合其中一张表出现次数≥1，另一张表出现次数>1的field_1值
3. 用筛选出的field_1值匹配两张表的所有对应行返回

示例SQL（通用标准SQL写法） #

WITH t1_count AS (
    SELECT field_1, COUNT(*) AS cnt
    FROM table_1
    GROUP BY field_1
),
t2_count AS (
    SELECT field_1, COUNT(*) AS cnt
    FROM table_2
    GROUP BY field_1
),
qualified_f1 AS (
    SELECT COALESCE(t1.field_1, t2.field_1) AS field_1
    FROM t1_count t1
    FULL OUTER JOIN t2_count t2 ON t1.field_1 = t2.field_1
    WHERE 
        -- 场景1：table_1至少1行，table_2多于1行
        (t1.cnt >= 1 AND t2.cnt > 1)
        OR
        -- 场景2：table_2至少1行，table_1多于1行
        (t2.cnt >= 1 AND t1.cnt > 1)
)
-- 取出table_1中符合条件的所有行
SELECT 'table_1' AS source_table, field_1, field_2, field_3, field_4
FROM table_1
WHERE field_1 IN (SELECT field_1 FROM qualified_f1)
UNION ALL
-- 取出table_2中符合条件的所有行
SELECT 'table_2' AS source_table, field_1, field_2, field_3, field_4
FROM table_2
WHERE field_1 IN (SELECT field_1 FROM qualified_f1)

语法适配说明 #

• 如果你使用的数据库不支持FULL OUTER JOIN（比如低版本MySQL），可以把qualified_f1部分替换为以下写法：

qualified_f1 AS (
    SELECT field_1 FROM t1_count WHERE field_1 IN (SELECT field_1 FROM t2_count)
    AND (
        (t1_count.cnt >=1 AND (SELECT cnt FROM t2_count WHERE t2_count.field_1 = t1_count.field_1) >1)
        OR
        (t1_count.cnt >1 AND (SELECT cnt FROM t2_count WHERE t2_count.field_1 = t1_count.field_1) >=1)
    )
)

• 返回结果里加了source_table字段用来标记行的来源表，不需要可以直接删除。

内容的提问来源于stack exchange，提问作者Ruby on Rails - MERN Stack