-- Requires that the name or Tel must have a duplicate 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 ', '123 '); insert into T values (2, 'zhang ', '20140901'); insert into T values (3, 'zhang', '20160901'); insert into T values (4, 'test', '123'); insert into T values (5, 'test', '123'); insert into T values (6, 'test1 ', '20140901'); insert into T values (7, 'tes T3 ', '123');/* Here you are. Thank you, huh, huh... */-- Result id367
Implementation Method with CTE:
; With AAS (select. *, B. ID as id2from T as a inner join t as B on (. name = B. name or. tel = B. tel) and. ID <> B. ID), BAS (select ID, name, tel from a as A2 where not exists (select 1 from a where (. name = a2.name or. tel = a2.tel) and. id2> a2.id) Select * From bunion select * from T as A2 where not exists (select 1 from a where id = a2.id)/* idnametel3zhang222226test1555557test333333 */
Original post: http://bbs.csdn.net/topics/390173231