--要求，name或tel有一个重复，则就算是重复的，取重复记录最大的id列表if object_id('t') is not null drop table tcreate 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');/*这个地方你来，先谢了，呵呵。。。*/--结果id367

用CTE实现方法:

;WITH aAS(SELECT     a.*,b.ID AS ID2FROM t AS a    INNER JOIN t AS b ON (a.name = b.name or a.tel = b.tel) AND a.ID<>b.ID),bAS(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 bUNION SELECT * FROM t AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE id=a2.ID)/*id	NAME	tel3	zhang	222226	test1	555557	test3	33333*/

原贴: