mysql查找值在多个列中重复

我正在编写一个代码，可以检查在员工表中多次出现的任何电话号码。在这种情况下，不同的人可能有相同的电话号码，我希望标记他们。

<table style="height: 234px;" width="735">
<tbody>
<tr>
<td style="width: 114px;"><span style="text-decoration: underline;"><strong>First name</strong></span></td>
<td style="width: 114px;"><span style="text-decoration: underline;"><strong>Last Name</strong></span></td>
<td style="width: 116px;"><span style="text-decoration: underline;"><strong>Main Phone</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Work Phone</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Mobile 1</strong></span></td>
<td style="width: 117px;"><span style="text-decoration: underline;"><strong>Mobile 2</strong></span></td>
</tr>
<tr>
<td style="width: 114px;">Jon</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">77777777</td>
<td style="width: 117px;">50505050</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">J&nbsp;</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">John&nbsp;</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">50505050</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
<tr>
<td style="width: 114px;">J Smith</td>
<td style="width: 114px;">&nbsp;</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">77777777</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">50505050</td>
</tr>
<tr>
<td style="width: 114px;">Jane</td>
<td style="width: 114px;">Smith</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">77777777</td>
</tr>
<tr>
<td style="width: 114px;">J</td>
<td style="width: 114px;">Doe</td>
<td style="width: 116px;">65656565</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">&nbsp;</td>
<td style="width: 117px;">33333333</td>
</tr>
<tr>
<td style="width: 114px;">Jessica</td>
<td style="width: 114px;">Doe</td>
<td style="width: 116px;">&nbsp;</td>
<td style="width: 117px;">33333333</td>
<td style="width: 117px;">65656565</td>
<td style="width: 117px;">&nbsp;</td>
</tr>
</tbody>
</table>

我试图搜索任何数字发生两次使用下面的代码没有成功，并征求您的意见。

Select firstname, lastname, mainPhone, count(mainPhone), workPhone,count(businessPhone), mobile1Phone, count(mobilePhone)  , mobile2Phone, count(mobile2Phone)  
from employeeTable
group by mainPhone, businessPhone, mobile1Phone, mobile2Phone
having 
(count(mainPhone) > 1) or (count(businessPhone) > 1) or (count(mobile2Phone) > 1) or (count(mobile2Phone) > 1);

你可以试试那样的。

SELECT
  phone,
  GROUP_CONCAT(`uniqueKey`) AS `uniqueKeys`,
  COUNT(*)
FROM
  (
  SELECT
    id AS `uniqueKey`,
    'mainPhone' AS source,
    mainPhone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'businessPhone' AS source,
    businessPhone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'mobile1Phone' AS source,
    mobile1Phone AS `phone`
  FROM
    employeeTable

  UNION

  SELECT
    id AS `uniqueKey`,
    'mobile2Phone' AS source,
    mobile2Phone AS `phone`
  FROM
    employeeTable
  ) AS subquery1
GROUP BY phone

首先用不同的语句连接起来 UNION 您可以创建类似于transpondet视图的内容。可以对其进行分组和计数。我假设有某种唯一的或主键，比如id、login或类似的。

mysql查找值在多个列中重复

1条答案

相关问题

热门标签

最新问答