先建立表:
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(100) DEFAULT NULL COMMENT 'name',
`ban` varchar(100) DEFAULT NULL COMMENT 'ban',
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `inx_ban` (`ban`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
name:学生名
ban:班级
score:分数
1、按班级分组排序,取出分数前两名的同学。
select t.ban,t.score,t.name from student t where 2<(select count(*) from student k where k.ban=t.ban and t.score>k.score order by k.ban desc) order by t.ban,t.score desc;
示例如下:
one 100 abin1
one 99 abin2
three 100 varyall1
three 99 varyall2
two 100 lee1
two 99 lee2
2、按组统计出来每组的所有分组,用逗号隔开
select t.ban,group_concat(t.score) from student t group by t.ban
示例如下:
one 100,99,97,95,91
three 100,99,97,95,91
two 100,99,97,95,91