1.建表语句
CREATE TABLE `score` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `student_id` INT(11) NOT NULL DEFAULT '0' COMMENT '学生表ID', `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '科目名称', `score` INT(11) NULL DEFAULT NULL COMMENT '分数', `cdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=5 ;
2.要求查询出同一学科时间最新的一条记录!
3.数据库中原始数据:
4.SQL语句如下:
方式一: select c.id,c.student_id,c.name,c.score,c.cdate from score c where c.cdate in (select max(s.cdate) from score s group by s.name) 方式二: select c.id,c.student_id,c.name,c.score,c.cdate from score c inner join (select s.name,max(s.cdate) as maxdate from score s group by s.name) b where c.name=b.name and c.cdate=b.maxdate
5.查询结果: