mysql - SQL:检测重复的客户

Question

我正在尝试创建一个 sql 查询，它将在我的数据库中检测(可能的)重复客户:

我有两个表:

1. Customer 列:cid、firstname、lastname、zip。请注意，cid 是此表的唯一客户 ID 和主键。
2. IgnoreForDuplicateCustomer 列:cid1、cid2。两列都是外键，引用了 Customer(cid)。这张表用来表示，cid1 的客户与 cid2 的客户不同。

例如，如果我有

• cid = 1、firstname="foo"、lastname="anonymous"和 zip="11231"的客户条目
• 和另一个带有 cid=2、firstname="foo"、lastname="anonymous"和 zip="11231"的客户条目。

所以我的 sql 查询应该搜索具有相同名字、姓氏和邮政编码的客户，并检测 cid = 1 的客户与 cid = 2 的客户相同。

但是，通过设置 cid1 = 1 和 cid2 = 2 在 IgnoreForDuplicateCustomer 表中存储一个新条目，应该可以说客户 cid = 1 和 cid=2 不同。

因此检测重复客户可以很好地使用此 sql 查询脚本:

SELECT cid, firstname, lastname, zip, COUNT(*) AS NumOccurrences
       FROM Customer
 GROUP BY fistname, lastname,zip
       HAVING ( COUNT(*) > 1 )

我的问题是，我无法将 IgnoreForDuplicateCustomer 表集成到与我之前的示例一样，cid = 1 和 cid=2 的客户不会被标记/查询为同一客户，因为 IgnoreForDuplicateCustomer 表中有一个条目/规则。

所以我尝试通过添加一个 where 子句来扩展我以前的查询:

    SELECT cid, firstname, lastname, COUNT(*) AS NumOccurrences
               FROM Customer    
    WHERE cid NOT IN (
                     SELECT cid1 FROM IgnoreForDuplicateCustomer WHERE cid2=cid 
                     UNION 
                     SELECT cid2 FROM IgnoreForDuplicateCustomer WHERE cid1=cid
                     )  
     GROUP BY firstname, lastname, zip
     HAVING ( COUNT(*) > 1 )

不幸的是，这个额外的 WHERE 子句对我的结果完全没有影响。有什么建议吗？

Answer 1

给你:

Select a.*
From (
  select c1.cid 'CID1', c2.cid 'CID2'
  from Customer c1 
  join Customer c2 on c1.firstname=c2.firstname 
    and c1.lastname=c2.lastname and c1.zip=c2.zip
    and c1.cid < c2.cid) a
Left Join (
  Select cid1 'CID1', cid2 'CID2'
  From ignoreforduplicatecustomer one
 Union
  Select cid2 'CID1', cid1 'CID2'
  From ignoreforduplicatecustomer two) b on a.cid1 = b.cid1 and a.cid2 = b.cid2
where b.cid1 is null

这将从 customer 表中获取重复记录的 ID，这些记录不在表 ignoreforduplicatecustomer 中。

测试:

CREATE TABLE IF NOT EXISTS `customer` (
 `CID` int(11) NOT NULL AUTO_INCREMENT,
 `Firstname` varchar(50) NOT NULL,
 `Lastname` varchar(50) NOT NULL,
 `ZIP` varchar(10) NOT NULL,
 PRIMARY KEY (`CID`)) 
ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=100 ;

INSERT INTO `customer` (`CID`, `Firstname`, `Lastname`, `ZIP`) VALUES
(1, 'John', 'Smith', '1234'),
(2, 'John', 'Smith', '1234'),
(3, 'John', 'Smith', '1234'),
(4, 'Jane', 'Doe', '1234');

和:

CREATE TABLE IF NOT EXISTS `ignoreforduplicatecustomer` (
 `CID1` int(11) NOT NULL,
 `CID2` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `ignoreforduplicatecustomer` (`CID1`, `CID2`) VALUES
(1, 2);

我的测试设置的结果是:

CID1  CID2
 1     3
 2     3