select * from tb a where exists(select 1 from tb where a2=a.a2 and a3<>a.a3 )
select a.* from tablea a where exists(select 1 from ta where a2 = a.a2 and a3 <> a.a3)
select * from A where a2 in ( select a2 from A group by a2 having count(*)>1 )
declare @t table (a int ,b int ) insert into @t select 1,1 insert into @t select 1,1 insert into @t select 2,3 insert into @t select 2,1 select a,b from @t group by a ,b having count(*)<=1
declare @t table (a int ,b int, c int ) insert into @t select 1,1,1 insert into @t select 1,1,1 insert into @t select 1,1,3 insert into @t select 2,3,1 insert into @t select 2,1,1select a,c from @t group by a ,c having count(*)<=1 --1 3
'上面错误' declare @t table (a int ,b int, c int ) insert into @t select 1,1,1 insert into @t select 1,1,1 insert into @t select 1,1,3 insert into @t select 1,1,2 insert into @t select 1,1,1 insert into @t select 2,3,1 insert into @t select 2,1,1 insert into @t select 2,1,2 select a,c from @t group by a ,c having count(*)<=(select top 1 count(*) from @t group by a,c order by count(*) desc) order by a /* 1 1 1 2 1 3 2 1 2 2 */
from tablea a
where exists(select 1 from ta where a2 = a.a2 and a3 <> a.a3)
(
select a2 from A group by a2 having count(*)>1
)
insert into @t select 1,1
insert into @t select 1,1
insert into @t select 2,3
insert into @t select 2,1
select a,b from @t group by a ,b having count(*)<=1
insert into @t select 1,1,1
insert into @t select 1,1,1
insert into @t select 1,1,3
insert into @t select 2,3,1
insert into @t select 2,1,1select a,c from @t group by a ,c having count(*)<=1
--1 3
declare @t table (a int ,b int, c int )
insert into @t select 1,1,1
insert into @t select 1,1,1
insert into @t select 1,1,3
insert into @t select 1,1,2
insert into @t select 1,1,1
insert into @t select 2,3,1
insert into @t select 2,1,1
insert into @t select 2,1,2
select a,c from @t group by a ,c having count(*)<=(select top 1 count(*) from @t group by a,c order by count(*) desc) order by a
/*
1 1
1 2
1 3
2 1
2 2
*/
a1 a2 a3
1 1 1
2 2 2
3 1 2
4 2 1
5 1 2
6 2 2
7 1 1
8 1 4
现在要去a2相同而a3不同的记录,结果就是这样
a1 a2 a3
1 1 1
2 2 2
3 1 2
4 2 1
8 1 4
declare @t table (a1 int ,a2 int, a3 int )insert into @t select 1 , 1 , 1
insert into @t select 2 , 2 , 2
insert into @t select 3 , 1 , 2
insert into @t select 4 , 2 , 1
insert into @t select 5 , 1 , 2
insert into @t select 6 , 2 , 2
insert into @t select 7 , 1 , 1
insert into @t select 8 , 1 , 4 select a2,a3 from @t group by a2 ,a3 having
count(*)<=(select top 1 count(*) from @t group by a2,a3 order by count(*) desc) order by a2
/*1 1
1 2
1 4
2 1
2 2
*/