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

问题描述

我试图匹配已购买相同商品的个客户，并按第一个客户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屋！