如何编写SQL查询获取在线商店中购买过相同商品的用户配对

原查询语句的问题点 #

• 关联条件指代缺失：B1.user = id未明确绑定到User U1.ID，多表场景下会触发列歧义报错，或生成无意义的笛卡尔积结果
• 无配对去重约束：未限制两个关联用户为不同个体，也未避免双向重复配对，会输出自配对（用户与自身匹配）、反向重复配对（2配3、3配2同时出现）的冗余结果
• 连接逻辑混乱：FROM子句后直接写两个User表的隐式笛卡尔积，再叠加显式JOIN，会放大无效计算量，容易得到不符合预期的结果

正确可运行的SQL查询 #

SELECT U1.Name AS 'User 1', U2.Name AS 'User 2'
FROM Buys B1
-- 关联购买了相同商品的其他用户记录
INNER JOIN Buys B2
  ON B1.Item = B2.Item
  -- 仅保留用户ID更小的配对组合，从根源排除自配对、重复反向配对
  AND B1.User < B2.User
-- 关联用户表查询对应姓名
INNER JOIN User U1
  ON B1.User = U1.ID
INNER JOIN User U2
  ON B2.User = U2.ID;

输出效果 #

基于预置的表数据，查询会匹配到Buys表中共同购买Item=20（Guitar）的两个用户，最终输出两列结果：

完全符合预期的配对输出要求。

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