如果匹配多个，如何区分？

Question

我想选择使用多个电话号码的人。电话号码匹配超过1该如何查询结果？

我已经在下面尝试过这个查询。

SELECT DISTINCT
    group,
    email,
    phone
FROM
    account
WHERE
    group = 6
GROUP BY
    phone
HAVING
    COUNT( phone ) > 1  
ORDER BY
    phone

group   email           phone
6       xxsd@adsf.xsd   003434234523
6       wet@ardsf.xsd   003434234523
6       rwwe@adsf.xsd   003364534579
6       hdgh@adsf.xsd   003434234523

因此，如果电话号码(003434234523)与1以上匹配，则将执行查询。

Answer 1

使用EXISTS查找使用同一电话的用户的电子邮件：

SELECT 
    a.group,
    a.email,
    a.phone
FROM
    account a
WHERE
    a.group = 6
    AND EXISTS (
      SELECT 1 FROM account 
      WHERE group = 6 AND phone = a.phone AND email <> a.email 
    )
ORDER BY a.phone

我使用WHERE group = 6是因为您的代码中有它。

Answer 2

这将从组6中的帐户表中查找使用多个帐户使用的电话号码的所有记录。

SELECT * 
FROM account
WHERE `group` = 6
AND phone IN (SELECT phone
              FROM account
              WHERE `group` = 6
              GROUP BY phone
              HAVING COUNT(*) > 1)
ORDER BY phone;

Answer 3

多个用户正在使用的相同电话号码。

重复电话：

   SELECT phone, count(*)
   FROM account
   WHERE group = 6
   GROUP BY phone
   HAVING COUNT(*) > 1

使用重复电话的电子邮件：

SELECT group, email, phone
FROM account
WHERE phone IN (
   SELECT phone, count(*)
   FROM account
   WHERE group = 6
   GROUP BY phone
   HAVING COUNT(*) > 1
)
ORDER BY phone