SQL关联两表出现重复结果：如何正确合并含主键、分类、分数的表

哦，我一眼就看出问题啦——你的JOIN条件只匹配了fruit字段，没把category也加进去！这就导致同一个水果下，两张表的所有分类都两两配对，直接产生了笛卡尔积，难怪结果里全是重复的行。

修正方法1：完善Full Outer Join的匹配条件 #

你需要把category也加入JOIN的关联条件中，确保每个(fruit, category)的唯一组合才会进行匹配，同时用COALESCE处理其中一张表没有该组合的情况，保证fruit和category字段不会为null：

WITH opinion_1 AS ( 
 SELECT 'apple' as fruit, 'color' as category, 15 as score 
 UNION ALL 
 SELECT 'apple', 'sweet', 50 
 UNION ALL 
 SELECT 'apple', 'scent', 35 
 UNION ALL 
 SELECT 'orange', 'color', 40 
 UNION ALL 
 SELECT 'orange', 'sweet', 60 
 ), opinion_2 AS ( 
 SELECT 'apple' as fruit, 'color' as category, 28 as score 
 UNION ALL 
 SELECT 'apple', 'sweet', 12 
 UNION ALL 
 SELECT 'orange', 'color', 29 
 UNION ALL 
 SELECT 'orange', 'sweet', 50 
 UNION ALL 
 SELECT 'orange', 'scent', 31 
 ) 
 SELECT 
   COALESCE(o1.fruit, o2.fruit) as fruit,
   COALESCE(o1.category, o2.category) as category,
   o1.score as score1,
   o2.score as score2
 FROM opinion_1 o1
 FULL OUTER JOIN opinion_2 o2 
   ON o1.fruit = o2.fruit 
   AND o1.category = o2.category;

修正方法2：兼容更多数据库的通用写法（适合不支持Full Outer Join的数据库如MySQL） #

先通过UNION获取两张表中所有不重复的(fruit, category)组合，再分别左连接两张表，这样就能保证每个组合都被列出，不存在的score自动显示为null：

WITH opinion_1 AS ( 
 SELECT 'apple' as fruit, 'color' as category, 15 as score 
 UNION ALL 
 SELECT 'apple', 'sweet', 50 
 UNION ALL 
 SELECT 'apple', 'scent', 35 
 UNION ALL 
 SELECT 'orange', 'color', 40 
 UNION ALL 
 SELECT 'orange', 'sweet', 60 
 ), opinion_2 AS ( 
 SELECT 'apple' as fruit, 'color' as category, 28 as score 
 UNION ALL 
 SELECT 'apple', 'sweet', 12 
 UNION ALL 
 SELECT 'orange', 'color', 29 
 UNION ALL 
 SELECT 'orange', 'sweet', 50 
 UNION ALL 
 SELECT 'orange', 'scent', 31 
 ), all_categories AS (
   SELECT fruit, category FROM opinion_1
   UNION
   SELECT fruit, category FROM opinion_2
 )
 SELECT 
   ac.fruit,
   ac.category,
   o1.score as score1,
   o2.score as score2
 FROM all_categories ac
 LEFT JOIN opinion_1 o1 
   ON ac.fruit = o1.fruit AND ac.category = o1.category
 LEFT JOIN opinion_2 o2 
   ON ac.fruit = o2.fruit AND ac.category = o2.category;

最终得到的正确结果 #

两种方法都会返回你期望的结果：

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