如何在MySQL中关联两张表仅返回column1无重复的交集结果

实现方案 #

核心思路 #

先过滤掉Table2中同一column1对应多条记录的无效数据，再和Table1做内关联即可得到符合要求的交集结果。

可直接运行的SQL（兼容MySQL5.x及以上版本） #

SELECT 
    t1.column2 AS `table1.column2`,
    t2.column2 AS `table2.column2`
FROM Table1 t1
-- 关联表2拿匹配的column2值
INNER JOIN Table2 t2 
    ON t1.column1 = t2.column1
-- 子查询过滤出表2中column1仅出现1次的有效值
INNER JOIN (
    SELECT column1
    FROM Table2
    GROUP BY column1
    HAVING COUNT(*) = 1
) valid_t2 
    ON t2.column1 = valid_t2.column1;

常见报错原因说明 #

你之前执行报错大概率是因为分组查询时直接选择了非分组字段（比如column2），不符合MySQL的ONLY_FULL_GROUP_BY模式约束。上面的写法中子查询仅返回过滤后的column1分组字段，完全兼容SQL标准，不会触发报错。

MySQL8.0+ 简化写法（窗口函数实现） #

如果你的数据库版本支持窗口函数，可以用更简洁的CTE写法：

WITH t2_with_count AS (
    SELECT 
        column1,
        column2,
        -- 统计每个column1对应的记录数
        COUNT(*) OVER(PARTITION BY column1) AS record_cnt
    FROM Table2
)
SELECT 
    t1.column2 AS `table1.column2`,
    t2.column2 AS `table2.column2`
FROM Table1 t1
INNER JOIN t2_with_count t2 
    ON t1.column1 = t2.column1 
    AND t2.record_cnt = 1;

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