SQL实现：查找与指定钱包有相同卡片的不同地址钱包

表结构 #

Wallets表 #

[id]   [address]
1      ABC
2      ABC
3      DEF
4      GHI
5      JKL

Cards表 #

[company] [color] [wallet_id]
Facebook   blue     1
Facebook   blue     2
Youtube    red      3
Facebook   blue     3
Orkut      pink     4 
Microsoft  green    5
Facebook   blue     5  

需求说明 #

传入指定钱包ID时，需返回满足以下条件的钱包：

1. 与目标钱包拥有至少一张完全相同的卡片（company和color均匹配）
2. 钱包地址与目标钱包地址不同

例如传入钱包ID=1时，预期返回：

[id]   [address]
3      DEF // 钱包1和3都有Facebook蓝色卡片
5      JKL // 钱包1和5都有Facebook蓝色卡片

注：钱包ID=2虽有相同卡片，但地址和ID1的ABC一致，因此不返回

已尝试的步骤 #

• 查询目标钱包基础信息：

  SELECT id, address FROM wallets w WHERE w.id = 1
  

• 查询目标钱包的所有卡片：

  SELECT company, color FROM cards c WHERE c.wallet_id = 1
  

• 尝试关联两个查询：

  SELECT id, address FROM wallets w INNER JOIN cards c ON w.id = c.id WHERE w.id = 1 GROUP BY id
  

目前卡在如何将上述结果与拥有相同卡片的其他钱包关联的环节。

解决方案 #

可以通过子查询先获取目标钱包的卡片集合，再关联Cards表找到所有匹配的钱包，最后排除地址相同的记录：

SELECT DISTINCT w.id, w.address
FROM wallets w
JOIN cards c ON w.id = c.wallet_id
-- 关联目标钱包的卡片组合
JOIN (
    SELECT company, color 
    FROM cards 
    WHERE wallet_id = 1 -- 替换为传入的目标钱包ID
) target_cards ON c.company = target_cards.company AND c.color = target_cards.color
-- 过滤掉与目标钱包地址相同的记录
WHERE w.address != (SELECT address FROM wallets WHERE id = 1)

逻辑解释 #

1. 子查询target_cards提取目标钱包的所有卡片组合
2. 通过JOIN匹配出所有拥有这些卡片的钱包
3. 用DISTINCT避免同一钱包因多张匹配卡片被重复返回
4. 最后通过WHERE条件排除地址与目标钱包一致的记录

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