mysql - 如何从MySQL表的多列中查找唯一记录

Question

我有一个有 45 列的表。 SrNo 列有一个主键。它有数百万条记录。它包含很多重复项。我实际上想找到唯一的值并将它们插入到另一个表中。 名字-中间名-姓氏、电子邮件(办公室、个人)、手机号码列包含大量重复项。我试图使用下面的查询查找唯一值，但它仍然显示重复的结果。谁能帮我看看我哪里错了？

SELECT ft.first_name
     , ft.middle_name
     , ft.last_name
     , ft.designation
     , ft.company_name
     , ft.email_office
     , ft.email_personal1
     , ft.email_personal2
     , ft.personal_mobile1
     , ft.personal_mobile2                                                                        
  FROM mytable ft    
  JOIN 
     ( SELECT First_Name
            , Middle_Name
            , Last_Name
            , designation
            , Company_Name
            , Email_Office
            , Email_Personal1
            , Email_Personal2
            , Personal_Mobile1
            , Personal_Mobile2
            , COUNT(*)
         FROM mytable
        GROUP 
           BY First_Name
            , Middle_Name
            , Last_Name
            , designation
            , Company_Name
            , Email_Office
            , Email_Personal1
            , Email_Personal2
            , Personal_Mobile1
            , Personal_Mobile2
       HAVING COUNT(*) = 1
     ) temp
    ON temp.first_name = ft.First_Name 
   AND temp.middle_name = ft.Middle_Name 
   AND temp.last_name = ft.Last_Name 
   AND temp.designation = ft.Designation 
   AND temp.company_name = ft.Company_Name 
   AND temp.email_office = ft.Email_Office 
   AND temp.email_personal1 = ft.Email_Personal1 
   AND temp.email_personal2 = ft.Email_Personal2 
   AND temp.personal_mobile1 = ft.Personal_Mobile1 
   AND temp.personal_mobile2 = ft.Personal_Mobile2
 WHERE srno <= 1000;     

对完整数据执行查询需要花费大量时间，因为它有数百万次。这就是为什么我必须添加 where 条件来显示最多 1000 条记录。

它在电子邮件中显示重复项，在名称中也显示了3列。谁能帮助我这个查询有什么问题吗？或者我这样会让事情变得更复杂吗？

另外，如何获取 count(*) > 1 的记录，如何仅获取其中的一条记录，同时忽略其他重复项？

下面是我的示例表表示。

+-----------+------------+----------+-------------------+-------------+----------------+-----------------+---------------------+------------+------------+
| FirstName | MiddleName | LastName | Designation       | CompanyName | Email(o)       | Email(P1)       | Email(P2)           | Mobile(pl) | Mobile(p2) | 
+-----------+------------+----------+-------------------+-------------+----------------+-----------------+---------------------+------------+------------+
| Anil      |            |          | Personnel Manager | ABC         | anilgabc.com   | anilggmail.com  |                     | 9898989898 |        | 
| Anil      | D          | Gupta    | Personnel Manager | ABC         | anilggmail.com |         |             |        |        | 
| Anil      | D          |          |                   | ABC         | anilg@abc.com  |         |             |        |        | 
| Anil      |            | Gupta    | Personnel Manager |             |                |                 | anilgreditfmail.com | 9898989898 |        | 
| Anil      |            | Gupta    |                   | ABC         |                |                 |                     | 9898989898 |        | 
| Anil      | D          | Gupta    | Personnel Manager | ABC         | anilgabc.com   | anilg@gmail.com | anilgrediffmail.com | 9898989898 |        | 
+-----------+------------+----------+-------------------+-------------+----------------+-----------------+---------------------+------------+------------+

这就是想要的结果...

Answer 1

首先对具有多个相同记录的表使用distinct，然后加入您需要的另一个表:像这样，，

     select  distinct    ft.first_name,ft.middle_name,ft.last_name,ft.designation,ft.company_name,ft.emai  l_office,ft.email_personal1, ft.email_personal2,  ft.personal_mobile1,     ft.personal_mobile2                                                                              from mytable ft    and so on