最终结果帖，非答案请勿回，毕竟大家时间保贵，多谢

最终结果帖，非答案请勿回，毕竟大家时间保贵，谢谢！
有兴趣请到此地址回帖，谢谢！
http://topic.****.net/u/20120813/19/2e35af97-6a4d-4dfe-ae06-bd1c05568299.html?88227

结果：3,6,7 【这种是指重复的取最大的，没重复的保留】
3,7都可以【这种就是取最大的含重复的id】

没办法，只能发200分的，因此发两个吧，此帖最终答案帖，谢谢！

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

探讨
当数据多了的时候，他是无限级判断的
SQL code

declare @name varchar(8)
set @name='test'

;with maco as
(
select id,name,tel from t where name=@name
union all
select a.* from t a ,maco b where a.tel=b.tel ……

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

SQL code


--大版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

最终结果帖，非答案请勿回，毕竟大家时间保贵，多谢

相关推荐