--> 测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (A varchar(1),B int,C varchar(2)) insert into #T select 'a',1,'3' union all select 'a',3,'5' union all select 'c',3,'df' union all select 'd',34,'34'select * from #T as t where b=(select min(b) from #T where a=t.a) /* A B C ---- ----------- ---- a 1 3 c 3 df d 34 34 */--如果B有重复用C,如果C也有重复,2000用临时表2005用row_number()生成行号
select * from tb as t where b=(select min(b) from tb where a=t.a)
select * from tb where b in (select max(b) from tb group by A)
select * from 表名 t where not exists(select 1 from 表名 where a=t.a and b<t.b) --or select * from 表名 t where (select count(distinct b) from 表名 where a=t.a and b<=t.b)=1
A B C ------------ a 1 3 a 3 5 c 3 df d 34 34 select * from tb a where not exists(select 1 from tb b where a.a=b.a and a.b<b.b)select * from tb a where a.b (select max(b.b) from tb b where a.a=b.a )
--手误 select * from tb a where a.b= (select max(b.b) from tb b where a.a=b.a )or select * from tb a where a.b in (select max(b.b) from tb b where a.a=b.a )
select * from tb a where a.b (select max(b.b) from tb b where a.a=b.a )
CREATE TABLE #person( sn varchar(10), name varchar(50) ); insert into #person values('1','guoqiang1'); insert into #person values('1','guoqiang1'); insert into #person values('1','guoqiang1'); insert into #person values('2','guoqiang2'); insert into #person values('2','guoqiang2'); insert into #person values('3','guoqiang3'); go select sn,name from (select *, row_number() over(partition by sn order by sn) as rank from #person) as t where t.rank = 1 go
create table tb(A varchar(4),B int,C varchar(5)) insert into tb select 'a',1,'3' union all select 'a',3,'5' union all select 'c',3,'df' union all select 'd',34,'34' select * from tb T where not exists(select 1 from tb where A = T.A and B<T.B) drop table tb
select * from tb as t where b=(select min(b) from tb where a=t.a)
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (A varchar(1),B int,C varchar(2))
insert into #T
select 'a',1,'3' union all
select 'a',3,'5' union all
select 'c',3,'df' union all
select 'd',34,'34'select * from #T as t where b=(select min(b) from #T where a=t.a)
/*
A B C
---- ----------- ----
a 1 3
c 3 df
d 34 34
*/--如果B有重复用C,如果C也有重复,2000用临时表2005用row_number()生成行号
select * from tb where b in (select max(b) from tb group by A)
select * from 表名 t where not exists(select 1 from 表名 where a=t.a and b<t.b)
--or
select * from 表名 t where (select count(distinct b) from 表名 where a=t.a and b<=t.b)=1
A B C
------------
a 1 3
a 3 5
c 3 df
d 34 34 select * from tb a
where not exists(select 1 from tb b where a.a=b.a and a.b<b.b)select * from tb a
where a.b (select max(b.b) from tb b where a.a=b.a )
select * from tb a
where a.b= (select max(b.b) from tb b where a.a=b.a )or
select * from tb a
where a.b in (select max(b.b) from tb b where a.a=b.a )
where a.b (select max(b.b) from tb b where a.a=b.a )
sn varchar(10),
name varchar(50)
); insert into #person values('1','guoqiang1');
insert into #person values('1','guoqiang1');
insert into #person values('1','guoqiang1');
insert into #person values('2','guoqiang2');
insert into #person values('2','guoqiang2');
insert into #person values('3','guoqiang3');
go
select sn,name from
(select *, row_number() over(partition by sn order by sn)
as rank from #person) as t
where t.rank = 1
go
create table tb(A varchar(4),B int,C varchar(5))
insert into tb select 'a',1,'3' union all select 'a',3,'5'
union all select 'c',3,'df' union all select 'd',34,'34'
select * from tb T where not exists(select 1 from tb where A = T.A and B<T.B)
drop table tb