--大版V5
;with a as
(
    select a.*,b.id bid from t a
    inner join t b 
    on (a.name = b.name or a.tel = b.tel) and a.id<>b.id
)

select distinct id from a a2 
where not exists(select 1 from a 
                 where (a.name = a2.name or a.tel = a2.tel)  and a.bid>a2.id)
/*
id
-----------
3
7
*/

------解决方案--------------------

怎么我写的那么复杂：
SQL code


with nameg as(
    --name group
    select tcount.*,ng.namegroup from 
    (
    select t.id,t.name,t.tel,row_number() over (partition by name order by id desc) as namenum
    from t) as tcount
    join 
    (
    select distinct name,row_number() over (order by name) as namegroup
    from t
    group by name
    ) as ng on tcount.name=ng.name
),
telg as(
--tel group
    select telcount.*,ng.telgroup from 
    (
    select id,name,tel,row_number() over (partition by tel order by id desc) as telnum 
    from t
    ) as telcount
    join 
    (
    select distinct tel,row_number() over (order by tel) as telgroup
    from t
    group by tel
    ) as ng on telcount.tel=ng.tel
),
 
mergeG as 
(
--merge
select nameg.*,telg.telnum,telg.telgroup from 
nameg
join telg on  nameg.id=telg.id
)



--select max(id) as gid from 
--(
--select mergeG.*,
--case when tempTelGroup.mergegroup is null then mergeG.namegroup 
--     else tempTelGroup.mergegroup
--end as CheckGroup

--from mergeG
--left join 
--(
----Get telCountnum
--select  telg.telgroup,MIN(mergeG.namegroup) as mergegroup
--from mergeG
--join telg on mergeG.telgroup=telg.telgroup and (mergeG.id!=telg.id)
--group by telg.telgroup) as tempTelGroup on mergeG.telgroup=tempTelGroup.telgroup

--) as t
--group by CheckGroup

select mergeG.*,
case when tempTelGroup.mergegroup is null then mergeG.namegroup 
     else tempTelGroup.mergegroup
end as CheckGroup

from mergeG
left join 
(
--Get telCountnum
select  telg.telgroup,MIN(mergeG.namegroup) as mergegroup
from mergeG
join telg on mergeG.telgroup=telg.telgroup and (mergeG.id!=telg.id)
group by telg.telgroup) as tempTelGroup on mergeG.telgroup=tempTelGroup.telgroup
order by CheckGroup,id desc