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怎么实现呢?
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怎么实现呢?
不知道对不对
还有这样得
貌似是这样也可以
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 a
inner join
(select 科目,max(分数) maxp
from xs group by 科目) b
on a.科目=b.科目 and a.分数=b.maxpid 姓名 科目 分数
----------- ---------- -------------------- -----------
7 王五 语文 94
3 张三 英语 99
8 王五 数学 88(3 row(s) affected)