数据表
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL DEFAULT '20' COMMENT '年龄',
`create_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 数据
insert into `student` (`id`, `name`, `score`, `create_date`) values('1','张三','11','2018-11-19 16:29:26');
insert into `student` (`id`, `name`, `score`, `create_date`) values('2','李四','21','2018-11-19 16:29:26');
insert into `student` (`id`, `name`, `score`, `create_date`) values('3','王五','31','2018-11-19 16:29:26');
insert into `student` (`id`, `name`, `score`, `create_date`) values('4','张三','12','2018-11-20 16:34:12');
insert into `student` (`id`, `name`, `score`, `create_date`) values('5','李四','22','2018-11-20 16:33:34');
insert into `student` (`id`, `name`, `score`, `create_date`) values('6','王五','32','2018-11-20 16:33:34');
问题： 如果我想按照name分组，并且按照时间获取最新的数据
SELECT * FROM test.student GROUP BY name ORDER BY create_date DESC;
这样的话，无法获取score为12,22,32的数据
方案： 采用group_concat() + substring_index()函数来解决问题
函数group_concat
# 分隔符 默认是 ,
group_concat([DISTINCT] 要连接的字段 [Order BY 排序字段 ASC/DESC] [Separator '分隔符'])
例如：SELECT name , GROUP_CONCAT(score ORDER BY create_date DESC SEPARATOR ',') scores FROM test.student GROUP BY name;
函数substring_index
substring_index(str,delim,count)
例：SELECT SUBSTRING_INDEX('blog.jb51.net', '.' ,2);
结果：blog.jb51
sql语句
SELECT SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY create_date DESC), ',', 1) id, name, SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY create_date DESC), ',', 1) score FROM test.student GROUP BY name;
