declare @test table(a int,b int,c int, d int,e int) insert into @test select 1,2,3,4,5 union all select 1,1,3,4,15 union all select 1,2,3,4,25 union all select 1,3,3,4,35 union all select 1,2,3,2,45 union all select 1,2,1,1,55 union all select 1,2,3,2,65 --假设你要检查字段a,b,c,d重复的记录 --开始查询 select * from @test t where exists(select 1 from @test where t.a=a and t.b=b and t.c=c group by a,b,c,d having count(1)>1) --测试结果 /* a b c d e ----------- ----------- ----------- ----------- ----------- 1 2 3 4 5 1 2 3 4 25 1 2 3 2 45 1 2 3 2 65 */
修正下declare @test table(a int,b int,c int, d int,e int) insert into @test select 1,2,3,4,5 union all select 1,1,3,4,15 union all select 1,2,3,4,25 union all select 1,3,3,4,35 union all select 1,2,3,2,45 union all select 1,2,1,1,55 union all select 1,2,3,2,65 --假设你要检查字段a,b,c,d重复的记录 --开始查询 select * from @test t where exists(select 1 from @test where t.a=a and t.b=b and t.c=c and t.d=d group by a,b,c,d having count(1)>1) --测试结果 /* a b c d e ----------- ----------- ----------- ----------- ----------- 1 2 3 4 5 1 2 3 4 25 1 2 3 2 45 1 2 3 2 65 */
declare @test table(a int,b int,c int, d int,e int)
insert into @test
select 1,2,3,4,5 union all
select 1,1,3,4,15 union all
select 1,2,3,4,25 union all
select 1,3,3,4,35 union all
select 1,2,3,2,45 union all
select 1,2,1,1,55 union all
select 1,2,3,2,65
--假设你要检查字段a,b,c,d重复的记录
--开始查询
select * from @test t
where exists(select 1 from @test
where t.a=a and t.b=b and t.c=c
group by a,b,c,d having count(1)>1)
--测试结果
/*
a b c d e
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
1 2 3 4 25
1 2 3 2 45
1 2 3 2 65
*/
insert into @test
select 1,2,3,4,5 union all
select 1,1,3,4,15 union all
select 1,2,3,4,25 union all
select 1,3,3,4,35 union all
select 1,2,3,2,45 union all
select 1,2,1,1,55 union all
select 1,2,3,2,65
--假设你要检查字段a,b,c,d重复的记录
--开始查询
select * from @test t
where exists(select 1 from @test
where t.a=a and t.b=b and t.c=c and t.d=d
group by a,b,c,d having count(1)>1)
--测试结果
/*
a b c d e
----------- ----------- ----------- ----------- -----------
1 2 3 4 5
1 2 3 4 25
1 2 3 2 45
1 2 3 2 65
*/