if exists(select name from sysobjects where name='tscore')
drop table tscore
go
create table tscore
(
id int identity(1,1) primary key not null,
name varchar(20) not null,
cname varchar(20) not null,
score int not null
)
go
insert into tscore values('关羽','语文',30)
insert into tscore values('关羽','语文',20)
insert into tscore values('关羽','语文',10)
insert into tscore values('张飞','数学',50)
insert into tscore values('张飞','数学',40)
insert into tscore values('张飞','数学',30)
insert into tscore values('赵云','英语',90)
insert into tscore values('赵云','英语',80)
insert into tscore values('赵云','英语',70)
查询结果：
'关羽','语文',30
'张飞','数学',50
'赵云','英语',90
sql 聚合
------解决方案--------------------
select name,cname,score from tscore a
where score=(select MAX (score) from tscore where a.name=name and a.cname=cname)
order by id
------解决方案--------------------
感觉有点奇怪，看不懂。
------解决方案--------------------
select name,cname,MAX(score) from tscore group by name,cname
------解决方案--------------------
正解