【SQL干货】求去重后的count数

如下简化后的数据表。 该表记录了每个商户的注册用户。  正常情况下，一个手机号不会被多个用户使用。由于历史原因，表里出现了这样的“一个手机号被多人使用”的数据。

 

✅ 现在要统计每个商户下，有多少手机号是重复的。手机号重复的定义是：手机号重复，姓名，身份证号不同叫手机号重复。

SELECT mer_id, count(1)
from (
    select mer_id,mobile,count(distinct name||idno)
    from (
     select 1 as mer_id,'13201010101' as mobile,'zhangsan' as name,'130434198801258888' as idno from dual union all
     select 1,'13312345678' , 'zhangsan', '130434198801258888' from dual union all
     select 1,'13201010101' , 'lisi', '120115198801256666' from dual union all
     select 1,'15688880000' , 'maliu', '31010419001020751X' from dual union all
     select 1,'18787654321' , 'maliu', '31010419001020751X' from dual union all
     select 1,'13312345678' , 'wangwu', '11011520101020118X' from dual union all
     select 1,'18787654321' , 'wangwu', '11011520101020118X' from dual union all     
     select 2,'13312345678' , 'wangwu', '11011520101020118X' from dual union all
     select 2,'13312345678' , 'maliu', '31010419001020751X' from dual
     ) a
     group by mer_id,mobile
     HAVING count(distinct name||idno)>1
 ) a
 group by mer_id;

结果：

 

✅ 全局统计，有多少手机号是重复的，不区分商户。

SELECT  count(1)
from (
    select mobile,count(distinct name||idno)
    from (
     select 1 as mer_id,'13201010101' as mobile,'zhangsan' as name,'130434198801258888' as idno from dual union all
     select 1,'13312345678' , 'zhangsan', '130434198801258888' from dual union all
     select 1,'13201010101' , 'lisi', '120115198801256666' from dual union all
     select 1,'15688880000' , 'maliu', '31010419001020751X' from dual union all
     select 1,'18787654321' , 'maliu', '31010419001020751X' from dual union all
     select 1,'13312345678' , 'wangwu', '11011520101020118X' from dual union all
     select 1,'18787654321' , 'wangwu', '11011520101020118X' from dual union all     
     select 2,'13312345678' , 'wangwu', '11011520101020118X' from dual union all
     select 2,'13312345678' , 'maliu', '31010419001020751X' from dual
     ) a
     group by mobile
     HAVING count(distinct name||idno)>1
 ) a

结果：