sql 查询最新的数据或某个最大值得数据

时间:2024-10-22 07:48:44

-- 新建分数表,该表记录学生最近几天本课程的所有分数
create table scores(
id int identity(1,1),
 name nvarchar(50) null,
 score float null,
 sdate nvarchar(50) null
)

-- 插入 li 同学本课程这几天的考试分数信息
insert into scores values('li',80,'2018-10-10 10:01:20')
insert into scores values('li',89,'2018-10-12 10:01:20')
insert into scores values('li',100,'2018-10-13 10:01:20')
insert into scores values('li',72,'2018-10-14 10:01:20')
insert into scores values('li',89,'2018-10-15 10:01:20')
-- 插入 liu 同学本课程这几天的考试分数信息
insert into scores values('liu',88,'2018-10-10 10:01:20')
insert into scores values('liu',90,'2018-10-12 10:01:20')
insert into scores values('liu',98,'2018-10-13 10:01:20')
insert into scores values('liu',88,'2018-10-14 10:01:20')
insert into scores values('liu',89,'2018-10-15 10:01:20')

-- 查询每个同学分数最高的一次考试信息
select * from scores where score=(select max(score)  from scores  s where =);

-- 查询每个同学最后一次的考试信息
select * from scores where sdate = (select max(sDate) from scores s where = );