时间戳

首页 > 解决方案 > MySQL查找在多列中重复的值

mysql - 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);

标签： mysql

解决方案

你可以尝试类似的东西。

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 或登录名或类似键。

推荐阅读