[ת]sql·Ö×éͳ¼Æ

--°´Ä³Ò»×ֶηÖ×éÈ¡×î´ó(С)ÖµËùÔÚÐеÄÊý¾Ý   
Êý¾ÝÈçÏ£º
name val memo
a    2   a2(aµÄµÚ¶þ¸öÖµ)
a    1   a1--aµÄµÚÒ»¸öÖµ
a    3   a3:aµÄµÚÈý¸öÖµ
b    1   b1--bµÄµÚÒ»¸öÖµ
b    3   b3:bµÄµÚÈý¸öÖµ
b    2   b2b2b2b2
b    4   b4b4
b    5   b5b5b5b5b5
*/
--´´½¨±í²¢²åÈëÊý¾Ý£º
create table tb(name varchar(10),val int,memo varchar(20))
insert into tb values('a',    2,   'a2(aµÄµÚ¶þ¸öÖµ)')
insert into tb values('a',    1,   'a1--aµÄµÚÒ»¸öÖµ')
insert into tb values('a',    3,   'a3:aµÄµÚÈý¸öÖµ')
insert into tb values('b',    1,   'b1--bµÄµÚÒ»¸öÖµ')
insert into tb values('b',    3,   'b3:bµÄµÚÈý¸öÖµ')
insert into tb values('b',    2,   'b2b2b2b2')
insert into tb values('b',    4,   'b4b4')
insert into tb values('b',    5,   'b5b5b5b5b5')
go
--Ò»¡¢°´name·Ö×éÈ¡val×î´óµÄÖµËùÔÚÐеÄÊý¾Ý¡£
--·½·¨1£º
select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--·½·¨2£º
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
--·½·¨3£º
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
--·½·¨4£º
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
--·½·¨5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
/*
name       val         memo