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

2012-09-10

最终结果帖，非答案请勿回，毕竟大家时间保贵，谢谢！有兴趣请到此地址回帖，谢谢！http://topic.csdn.net/u/201

最终结果帖，非答案请勿回，毕竟大家时间保贵，谢谢！
有兴趣请到此地址回帖，谢谢！
http://topic.csdn.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-----------37*/
[解决办法]
怎么我写的那么复杂：
SQL codewith 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 (--mergeselect nameg.*,telg.telnum,telg.telgroup from namegjoin 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 CheckGroupselect mergeG.*,case when tempTelGroup.mergegroup is null then mergeG.namegroup      else tempTelGroup.mergegroupend as CheckGroupfrom mergeGleft join (--Get telCountnumselect  telg.telgroup,MIN(mergeG.namegroup) as mergegroupfrom mergeGjoin telg on mergeG.telgroup=telg.telgroup and (mergeG.id!=telg.id)group by telg.telgroup) as tempTelGroup on mergeG.telgroup=tempTelGroup.telgrouporder by CheckGroup,id desc

热点排行

SQL Server

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