--要求，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 aINNER 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

轉載于:https://www.cnblogs.com/wuxi88/archive/2013/01/02/5466750.html

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的取重复记录最大的id列表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。