select a.id,x.num
from table1 as a left outer join (select num
from table1
group by num
having count(*)<>1) as x
on a.num=x.num
where x.num is not null
from table1 as a left outer join (select num
from table1
group by num
having count(*)<>1) as x
on a.num=x.num
where x.num is not null
where num in
(select num from table1 group by num having sum(1)>1)
where exists(select 1 from table1 where num=a.num and id<>a.id)
declare @t table(id int,num int)
insert into @t
select 1,3
union all select 2,3
union all select 3,4
union all select 4,6
union all select 5,7
union all select 6,8
union all select 7,9
union all select 8,8
union all select 9,8--查询
select * from @t a
where exists(select 1 from @t where num=a.num and id<>a.id)/*--结果
id num
----------- -----------
1 3
2 3
6 8
8 8
9 8(所影响的行数为 5 行)--*/
from tablename temp1
where id <> (select max(id) from tablename temp2 where temp1.num = temp2.num)删除表中的重复数据,保留重复数据id最大的记录