继续这个有关问题

继续这个问题
create table xs(
id int IDENTITY (1,1) primary key,
姓名 char(10),
科目 char(20),
分数 int)
insert into xs values('张三','语文',90)
insert into xs values('张三','数学',77)
insert into xs values('张三','英语',99)
insert into xs values('李四','语文',83)
insert into xs values('李四','数学',87)
insert into xs values('李四','英语',85)
insert into xs values('王五','语文',94)
insert into xs values('王五','数学',88)
insert into xs values('王五','英语',93)

查询各科成绩最高分的学生得ID、姓名、科目、分数

 请问用ACCESS怎么实现呢？


[解决办法]

SQL code

create table xs(id int IDENTITY (1,1) primary key,姓名 char(10),科目 char(20),分数 int)insert into xs values('张三','语文',90)insert into xs values('张三','数学',77)insert into xs values('张三','英语',99)insert into xs values('李四','语文',83)insert into xs values('李四','数学',87)insert into xs values('李四','英语',85)insert into xs values('王五','语文',94)insert into xs values('王五','数学',88)insert into xs values('王五','英语',93)select a.* from xs ainner join (select 科目,max(分数) maxpfrom xs group by 科目) bon a.科目=b.科目 and a.分数=b.maxpid          姓名         科目                   分数----------- ---------- -------------------- -----------7           王五         语文                   943           张三         英语                   998           王五         数学                   88(3 row(s) affected)