declare @tb table(a int,b int,c int)
insert into @tb
select 1,1,1
union
select 1,1,2
union
select 2,1,3
union
select 2,1,4
union
select 3,1,4
union
select 3,1,2
union
select 4,4,4select *
from @tb t
where not exists (select 1 from @tb where a=t.a and b = t.b and c <> t.c)
1,1,1
2,1,4
3,1,4
4,4,4
过滤条件a=a b=a c<>c 的记录中只显示一条
from @tb t
where not exists (select 1 from @tb where a=t.a and b = t.b and c <> t.b
and c <> t.a)
--这样?
1,1,1
1,1,2
只显示其中的一条,相当于去掉a=a,b=b的重复
select *
from tb t
where not exists (select 1 from tb where a=t.a and b=t.b and c>t.c)
select a,b,c
from(
select *,rid=row_number() over (partition by a,b order by getdate())
from tb
)t
where rid = 1
select a,b,c,rid=identity(int,1,1)
into #tb
from tbselect a,b,c
from #tb t
where rid = (select top 1 rid from tb where a=t.a and b=t.b order by c)
from @tb
where a = b
group by a,b
--这样?
from @tb t
where not exists (select 1 from @tb where a=t.a and b = t.b and c <> t.c)???
insert into @tb
select 1,1,1
union
select 1,1,2
union
select 2,1,3
union
select 2,1,4
union
select 3,1,4
union
select 3,1,2
union
select 4,4,4select *
from @tb t
where not exists (select 1 from @tb where a=t.a and b = t.b and c > t.c)/*
(7 行受影响)
a b c
----------- ----------- -----------
1 1 2
2 1 4
3 1 4
4 4 4(4 行受影响)