根据mysql表中的另一列查找表中两列的重复项

Question

我有一个表来存储电话号码

电话号码

--------------------------------
id | primary | phone1 | phone2  |
--------------------------------
1  | phone1  |  xxx   |  xxx    |
2  | phone2  |  xxx   |  xxx    | 
3  | phone2  |  xxx   |  xxx    | 
4  | phone1  |  xxx   |  xxx    |
5  | phone2  |  xxx   |  xxx    |
6  | phone1  |  xxx   |  xxx    |

如何从表中查找主电话号码的重复条目(和相应记录(？如果主要是phone1，则值存储在phone1中；如果主要是phone2，则值将存储在phone2字段中

Answer 1

如何从表中查找主电话号码的重复条目(和相应记录(？

如果要显示整个原始记录，最简单的选项可能是exists和条件表达式：

select t.*
from mytable t
where exists (
select 1 
from mytable t1
where 
case when t1.primary = 'phone1' then t1.phone1 else t1.phone2 end
= case when t.primary = 'phone1' then t.phone1 else t.phone2 end
and t1.id <> t.id
)

在MySQL 8.0中，一种替代方法使用窗口计数：

select *
from (
select t.*,
count(*) over(partition by case when primary = 'phone1' then phone1 else phone2 end) as cnt
from mytable t
) t
where cnt > 1

Answer 2

您应该修复数据模型，并将手机存储在单独的行上。如果没有，您可以取消透视并聚合以查找不同列中的数字：以下返回多次出现的所有电话以及存储电话的列：

select phone, sum(is_primary) as num_primary, sum(is_1) as num_1, num(is_2) asnum2
from ((select primary as phone, 1 as is_primary, 0 as is_1, 0 as is_2
from phone_numbers pn
) union all
(select phone1, 0 as is_primary, 1 as is_1, 0 as is_2
from phone_numbers pn
) union all
(select phon2, 0 as is_primary, 0 as is_1, 1 as is_2
from phone_numbers pn
) 
) t
where phone is not null
group by phone
having count(*) > 1;