取重复记录最大的id列表

--要求，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');
/*
这个地方你来，先谢了，呵呵。。。
*/
--结果
id
3
6
7

用CTE实现方法:

;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