在 SQL 中匹配对时收到额外的行 [英] Extra rows being received when matching pairs in SQL

问题描述

我正在尝试匹配购买相同商品的 customers，按第一个客户 ID CID 排序.该查询产生了正确的结果，但我得到的行数比我应该收到的多大约 37 行.

I am attempting to match customers who have purchased the same item, ordered by the first customer id CID. The query produces correct results but I am getting approximately 37 more rows than I should be receiving.

经检查，在这个意义上似乎有一些重复

Upon inspection there appears to be some duplicates in this sense

Customer A | Customer B
Customer B | Customer A

这仅发生在某些匹配项上，而不发生在其他匹配项上

This only occurs for some matches but not others

SELECT DISTINCT ca.name as CUSTOMERA, cb.name as CUSTOMERB
FROM customer ca, customer cb
INNER JOIN YRB_PURCHASE pur1 ON ca.cid = pur1.cid
INNER JOIN YRB_PURCHASE pur2 ON cb.cid = pur2.cid
WHERE pur1.title = pur2.title 
  AND ca.cid > cb.cid;

这是一个来自数据库的小例子

Here is a small example from the database

Jon Stewart  | Sydney Crosby
Jake Banning | James Monroe
James Monroe | Jake Banning

不应返回最后一行，因为 Jake Banning 和 James Monroe 已经在第 2 行配对

The last row shouldn't be returned as Jake Banning and James Monroe have already been paired up in row 2

推荐答案

如果你只想要第一个名字按字母顺序排列在第二个之前的行，那么告诉 SQL 就行了

If you only want rows where the first name is alphabetically before the second, well just tell SQL that

SELECT DISTINCT ca.name as CUSTOMERA, cb.name as CUSTOMERB
FROM customer ca, customer cb
INNER JOIN YRB_PURCHASE pur1 ON ca.cid = pur1.cid
INNER JOIN YRB_PURCHASE pur2 ON cb.cid = pur2.cid
WHERE pur1.title = pur2.title 
  AND ca.name < cb.name;

这篇关于在 SQL 中匹配对时收到额外的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！