SQL语句表 TTA B C 字段
1 18 12
2 19 12
3 20 12
4 18 12
5 18 22 结果
1 18 12
2 19 12
3 20 12
5 18 22忽略 B C 相同的记录
1 18 12
2 19 12
3 20 12
4 18 12
5 18 22 结果
1 18 12
2 19 12
3 20 12
5 18 22忽略 B C 相同的记录
( A int,B int, C int)insert into #TT
select 1,18,12
union all
select 2,19,12
union all
select 3,20,12
union all
select 4,18,12
union all
select 5,18,22
select * from #TT group by B,C having A=min(A)
drop table #TT
insert @t
select 1, 18, 12 union all
select 2, 19, 12 union all
select 3, 20, 12 union all
select 4, 18, 12 union all
select 5, 18, 22 ----方法1:
select * from @t as a where not exists(select 1 from @t where B = a.B and C =a.c and A < a.A)
----方法2:
select * from @t as a where A = (select min(A) from @t where B = a.B and C =a.c )/*结果
1 18 12
2 19 12
3 20 12
5 18 22
*/
insert test select 1,18,12
union all select 2,19,12
union all select 3,20,12
union all select 4,18,12
union all select 5,18,22select A,B,C from test t
where not exists
(
select 1 from test where B=t.B and C=t.C and A<t.A
)
真帅,经测试,完全正确
insert into @t(a,b,c) select 1, 18 , 12 union all select
2, 19, 12 union all select
3, 20, 12 union all select
4, 18, 12 union all select
5 , 18, 22 select A,B,C from @T AS T WHERE EXISTS(SELECT * FROM @T B WHERE T.B<>B.B AND T.C<>B.C) UNION select TOP 1 A,B,C from @T AS T WHERE EXISTS(SELECT * FROM @T B WHERE T.B=B.B AND T.C=B.C) ORDER BY A