sql 查询问题

[mmkkuoi]

有一张表

id name count
1 aa 1
2 aa 2

3 bb 1
4 bb 2
5 bb 3

6 cc 1
7 cc 3

我希望得到的结果是
id name count
2 aa 2
5 bb 3
7 cc 3

就是的到count 最大的结果集 ，
如果后面增加一列 id name count (8,ee,1);

我希望得到的结果是
id name count
2 aa 2
5 bb 3
7 cc 3
8 ee 1

[-晴天]

create table tb(id int,name varchar(10),cnt int)
insert into tb select 1,'aa',1
insert into tb select 2,'aa',2
insert into tb select 3,'bb',1
insert into tb select 4,'bb',2
insert into tb select 5,'bb',3
insert into tb select 6,'cc',1
insert into tb select 7,'cc',3
insert into tb select 8,'ee',1
go
select * from tb a where not exists(select 1 from tb where name=a.name and cnt>a.cnt)
go
drop table tb
/*
id          name       cnt
----------- ---------- -----------
2           aa         2
5           bb         3
7           cc         3
8           ee         1

(4 行受影响)

*/

[快溜]

select id,name,[count]
 from (select row_number over(parttion by name order by [count] desc) as no,* 
         from tb) a where a.no=1

[快溜]

create table tb(id int,name varchar(10),cnt int)
insert into tb select 1,'aa',1
insert into tb select 2,'aa',2
insert into tb select 3,'bb',1
insert into tb select 4,'bb',2
insert into tb select 5,'bb',3
insert into tb select 6,'cc',1
insert into tb select 7,'cc',3
insert into tb select 8,'ee',1

select id,name,cnt
 from (select row_number over(parttion by name order by cnt desc) as no,* 
         from tb) a where a.no=1
/*
id          name       cnt
----------- ---------- -----------
2           aa         2
5           bb         3
7           cc         3
8           ee         1

/*

[Andy-W]

id name count

Select id ,name, count

[Andy-W]

Select id ,name, count From tb As a Where a.count=(Select Max(count) From tb Where name=a.name)

[快溜]

create table tb(id int,name varchar(10),cnt int)
insert into tb select 1,'aa',1
insert into tb select 2,'aa',2
insert into tb select 3,'bb',1
insert into tb select 4,'bb',2
insert into tb select 5,'bb',3
insert into tb select 6,'cc',1
insert into tb select 7,'cc',3
insert into tb select 8,'ee',1

select id,name,cnt
 from (select row_number over(partition by name order by cnt desc) as no,* 
         from tb) a where a.no=1
/*
id          name       cnt
----------- ---------- -----------
2           aa         2
5           bb         3
7           cc         3
8           ee         1

/*