in和exists例子

一个表中的某一列要重复数据,只要这些重复数据中的一个,表中有主键(或自增字段)
解决方法
if object_id('student') is not null
drop table student
go
create table student(id int identity,name varchar(1))
insert into student select 'a'
union all select 'a'
union all select 'b'
union all select 'b'

select * from student
结果
id name
----------- ----
1 a
2 a
3 b
4 b
select * from student where exists(select 1 from (select max(id) as id,name from student group by name) b where id=student.id)

结果
id name
----------- ----
2 a
4 b

select * from student where id in ( select id from (select max(id) as id,name from student group by name) b )

结果
id name
----------- ----
2 a
4 b