--测试--测试数据 create table tb(fKey int,fA int,fB int) insert tb select 1,5,4 union all select 2,5,7 union all select 3,5,3 union all select 4,5,4 union all select 5,3,9 union all select 6,5,3 union all select 7,5,9 go--查询 select * from tb a where not exists( select * from tb where FA=a.FA and FB=a.FB and fKey<a.fKey) go--删除测试 drop table tb/*--测试结果 fKey fA fB ----------- ----------- ----------- 1 5 4 2 5 7 3 5 3 5 3 9 7 5 9(所影响的行数为 5 行) --*/
--或者:select a.* from tb a,(select fKey=min(fKey) from tb group by fA,fB)b where a.fKey=b.fKey
这样效率高些。Select A.* from TableName A Inner Join (Select Min(fKey) As fKey,fA,fB from TableName Group By fA,fB) B On A.fKey=B.fKey Order By A.fKey
select * from select distinct fa,fb from tablename
select a.* from 表 a where not exists(select 1 from 表 where fA=a.fA and fB=a.fB and fKey<a.fKey) order by a.fKey
select fKey=min(fKey),fA,fB from tb group by fA,fB order by min(fkey)
--测试--测试数据 create table tb(fKey int,fA int,fB int) insert tb select 1,5,4 union all select 2,5,7 union all select 3,5,3 union all select 4,5,4 union all select 5,3,9 union all select 6,5,3 union all select 7,5,9 go--查询 select min(fKey) as fKey,fA,fB from tb group by fA,fB order by fKey go--删除测试 drop table tb
想复杂了,直接Select Min(fKey) As fKey,fA,fB from TableName Group By fA,fB Order By fKey
create table tb(fKey int,fA int,fB int)
insert tb select 1,5,4
union all select 2,5,7
union all select 3,5,3
union all select 4,5,4
union all select 5,3,9
union all select 6,5,3
union all select 7,5,9
go--查询
select * from tb a
where not exists(
select * from tb where FA=a.FA and FB=a.FB and fKey<a.fKey)
go--删除测试
drop table tb/*--测试结果
fKey fA fB
----------- ----------- -----------
1 5 4
2 5 7
3 5 3
5 3 9
7 5 9(所影响的行数为 5 行)
--*/
from tb a,(select fKey=min(fKey) from tb group by fA,fB)b
where a.fKey=b.fKey
Inner Join
(Select Min(fKey) As fKey,fA,fB from TableName Group By fA,fB) B
On A.fKey=B.fKey
Order By A.fKey
a.*
from
表 a
where
not exists(select 1 from 表 where fA=a.fA and fB=a.fB and fKey<a.fKey)
order by
a.fKey
create table tb(fKey int,fA int,fB int)
insert tb select 1,5,4
union all select 2,5,7
union all select 3,5,3
union all select 4,5,4
union all select 5,3,9
union all select 6,5,3
union all select 7,5,9
go--查询
select min(fKey) as fKey,fA,fB from tb group by fA,fB order by fKey
go--删除测试
drop table tb