一个查询的问题 求帮忙
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 intnot 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
