php - 为mysql中的重复字段选择company_id

Question

我想从重复的字段中选择 company_id。

>   
> -------------------------------------------------------------------- 
| id  |  company_id | contact_name  | ph_no  | mobile_no | email        | 
> ---------------------------------------------------------------------- 
| 1   | 402         | shuhaib       |1234    |7788       |abc@rain.com  | 
| 2   | 402         | salih         |1234    |7877       |xqw@rain.com  | 
| 3   | 410         | musammil      |1234    |4545       |abc@rain.com  | 
| 4   | 411         | haris         |1234    |9495       |wew@rain.com  | 
| 5   | 412         | dileep        |5467    |5852       |bgf@rain.com  |
| 6   | 412         | wahab         |8019    |9858       |mng@rain.com  | 
| 11  | 414         | jithesh       |7025    |9495       |trf@rain.com  | 
> ----------------------------------------------------------------------

当 ph_no = ph_no 或 mobile_no = mobile_no 或 email = email 时，我想从上面的单个表中获取 company_id 和 duplicate_company_id，只选择这两个 id，其中 company_id != duplicate_company_id 。结果是

 ---------------------------------------
| company_id    | duplicate_company_id |
 ---------------------------------------
| 402           | 410                  | because same ph_no ,email
| 402           | 411                  | because same ph_no   
| 411           | 414                  | because same mobile_no
  --------------------------------------

有时我的查询不起作用

SELECT a.company_id,
       max(b.id) AS duplicate_company_id
FROM
  (SELECT company_id,
          ph_no,
          mobile_no,
          email
   FROM crm_customer_contacts
   WHERE IFNULL(ph_no, '') != ''
     OR IFNULL(mobile_no, '') != ''
     OR IFNULL(email, '') != ''
   GROUP BY ph_no,
            mobile_no,
            email HAVING count(company_id) > 1
   ORDER BY company_id) a
JOIN crm_customer_contacts b ON b.company_id != a.company_id
AND (a.ph_no = b.ph_no
     OR a.mobile_no = b.mobile_no
     OR a.email = b.email )
GROUP BY a.company_id
ORDER BY company_id

Answer 1

select A.company_id,B.company_id as duplicate_company_id
from company_table A, company_table B
where A.id != B.id
AND
A.company_id != B.company_id
AND
(
    A.ph_no = B.ph_no
    OR
    A.mobile_no = B.mobile_no
    OR
    A.email = B.email
)