declare @tb table (id int,name varchar(20)) insert into @tb select 1,'456' insert into @tb select 2,'123' insert into @tb select 3,'456' insert into @tb select 4,'789' insert into @tb select 5,'456'insert into @tb select 6,'798' insert into @tb select 7,'741' insert into @tb select 8,'852' insert into @tb select 9,'963' insert into @tb select 10,'123'select * from @tb t where exists( select 1 from @tb where name=t.name and id<>t.id )id name 1 456 2 123 3 456 5 456 10 123
declare @tb table (id int,name varchar(20)) insert into @tb select 1,'456' insert into @tb select 2,'123' insert into @tb select 3,'456' insert into @tb select 4,'789' insert into @tb select 5,'456'insert into @tb select 6,'789' insert into @tb select 7,'741' insert into @tb select 8,'852' insert into @tb select 9,'963' insert into @tb select 10,'123'select * from @tb t where exists( select 1 from @tb where name=t.name and id<>t.id )id name 1 456 2 123 3 456 4 789 5 456 6 789 10 123
--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (id int,name int) insert into #T select 1,456 union all select 2,123 union all select 3,456 union all select 4,789 union all select 5,456 union all select 6,789 union all select 7,741 union all select 8,852 union all select 9,963 union all select 10,123select * from #T as t where exists (select 1 from #T where name=t.name and id<>t.id) /* id name 1 456 2 123 3 456 4 789 5 456 6 789 10 123 */
select * from #T as t where exists (select 1 from #T where name=t.name and id<>t.id) 这个就对。
select * from Test as t where exists (select 1 from Test where name=t.name and id<>t.id)
insert into @tb select 1,'456'
insert into @tb select 2,'123'
insert into @tb select 3,'456'
insert into @tb select 4,'789'
insert into @tb select 5,'456'insert into @tb select 6,'798'
insert into @tb select 7,'741'
insert into @tb select 8,'852'
insert into @tb select 9,'963'
insert into @tb select 10,'123'select * from @tb t
where exists(
select 1 from @tb where name=t.name and id<>t.id
)id name
1 456
2 123
3 456
5 456
10 123
insert into @tb select 1,'456'
insert into @tb select 2,'123'
insert into @tb select 3,'456'
insert into @tb select 4,'789'
insert into @tb select 5,'456'insert into @tb select 6,'789'
insert into @tb select 7,'741'
insert into @tb select 8,'852'
insert into @tb select 9,'963'
insert into @tb select 10,'123'select * from @tb t
where exists(
select 1 from @tb where name=t.name and id<>t.id
)id name
1 456
2 123
3 456
4 789
5 456
6 789
10 123
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (id int,name int)
insert into #T
select 1,456 union all
select 2,123 union all
select 3,456 union all
select 4,789 union all
select 5,456 union all
select 6,789 union all
select 7,741 union all
select 8,852 union all
select 9,963 union all
select 10,123select * from #T as t where exists (select 1 from #T where name=t.name and id<>t.id)
/*
id name
1 456
2 123
3 456
4 789
5 456
6 789
10 123
*/
这个就对。