取重复记录最大的id列表

[sql] www.2cto.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.2cto.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