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)