取重复记录最大的id列表

取重复记录最大的id列表

 

[sql]  www.zzzyk.com  

--要求，name或tel有一个重复，则就算是重复的，取重复记录最大的id列表  

if object_id('t') is not null drop table t  

create table t(  

id int,  

name varchar(10),  

tel varchar(10)  

)  

insert into t values(1,'zhang','11111');  

insert into t values(2,'zhang','11111');  

insert into t values(3,'zhang','22222');  

insert into t values(4,'test','33333');  

insert into t values(5,'test','12345');  

insert into t values(6,'test1','55555');  

insert into t values(7,'test3','33333');  

/*  

这个地方你来，先谢了，呵呵。。。  

*/  

--结果    www.zzzyk.com  

id  

3  

6  

7  

 

用CTE实现方法:

[sql] 

;WITH a  

AS  

(  

SELECT   

    a.*,b.ID AS ID2  

FROM t AS a  

    INNER JOIN t AS b ON (a.name = b.name or a.tel = b.tel) AND a.ID<>b.ID  

),b  

AS  

(  

SELECT id,NAME,tel FROM a  AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE (a.name = a2.name or a.tel = a2.tel)  AND a.ID2>a2.ID)  

)  

SELECT * FROM b  

UNION   

SELECT * FROM t AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE id=a2.ID)  

/*  

id  NAME    tel  

3   zhang   22222  

6   test1   55555  

7   test3   33333  

*/  

 

来源:http://bbs.csdn.net/topics/390173231