mysql - 选择 ID 相同但值不同的行

Question

我想从表 USERS 中获得一个列表，该列表为我提供具有相同 COMPANYID 但地址 1 或地址 2 不匹配的所有用户。

| USER  |COMPANYID| ADDRESS 1     | ADDRESS 2
|     1 | A       | Street A     | 11
|     2 | A       | Street A     | 11
|     3 | B       | Street B     | 12
|     4 | B       | Street B     | 13
|     5 | C       | Street C     | 14
|     6 | C       | Street C     | 14
|     7 | C       | Street C     | 15
|     8 | D       | Street C     | 15

我想获取 COMPANYID 存在不止一次但公司用户之间的地址不匹配的所有行。这就是我想要得到的:

| userID| COMPANYID| ADDRESS 1    | ADDRESS 2
|     3 | B        | Street B     | 12
|     4 | B        | Street B     | 13
|     5 | C        | Street C     | 14
|     6 | C        | Street C     | 14
|     7 | C        | Street C     | 15

我可以得到查询的第一部分:

SELECT *FROM USERS WHERE comapnyID IN (SELECT companyID FROM USERS GROUP BY companyID HAVING COUNT(*)>1) ORDER BY companyID

但我不想包括地址已经匹配的地址。

Answer 1

这很难:)

select * from USERS where companyid in (
    select t1.companyid  from (
        SELECT n.companyid,n.address1,n.address2,
            CASE WHEN n.address1=o.address1 AND n.address2=o.address2 THEN "No Change"
            ELSE "Changed"
            END as ChangeFlag
        FROM USERS n
        LEFT OUTER JOIN USERS o ON n.companyid=o.companyid
        ) t1
    where ChangeFlag="Changed")
 order by 1,2