现在假设环境需求如下图:
表中有不同学校、学校班级、班级的组、每个组的学生的信息;
现在需要获取的是取所有学校里每个组第一个学生的信息
(这里并不完全实际情况,学生位子布置跟名字排序相关的。此处做例子,阐释一个排序方式。)
纯粹结果集加上一个序号列:
SELECT @i:=@i+1 id,a.`name`,a.`group`,a.`class` FROM bb a,(SELECT @i:=0) b;
将学生信息按照学校、班级、组编号,获取指定学员部分信息
SELECT
a.`school`
,a.`class`
,a.`group`
,a.`name`
,IF(@sch=a.group,@rank:=@rank+1,@rank:=1) AS ranks
,@num:=@num+1 num
,@cls:=a.class class_id
,@sch:=a.group group_id
FROM
(
SELECT a.`school`,a.`class`,a.`group`,a.`id`,a.`name`
FROM `bb` a
ORDER BY a.`school`,a.`class`
) a,
(SELECT @num:=0,@rank:=0,@cla:=NULL,@sch:=NULL) b;
执行结果如下:
使@cls和@sch的值分别等于字段class和school的值,最终按照group来排名,
也就是 内容(@sch=a.group,@rank:=@rank+1,@rank:=1) 的意思:同一个组的学生,进行排序,从1开始,依次递增。当组号不同时,开始新的排序。
最终获取每个组排序后指定编号的那些记录即可。
如:只要每组第一个学生,则ranks值为1的记录;
如:只要每组前三个学生,则ranks值<4的记录;
另外,使用函数,find_in_set也有意想不到的排序效果;如下例:
(1)
SELECT * FROM bb WHERE id IN (2,1,3,5,4) ;
只是查询id in (2,1,3,5,4)的时候,出来的结果,默认排序依旧是 id 列的升序;
(2)
SELECT * FROM bb WHERE id IN (2,1,3,5,4) ORDER BY FIND_IN_SET(NAME,'AA,bb,cc,ee,dd');
当加上find_in_set,并且按照name列,指定顺序为(AA,bb,cc,ee,dd ),出来的结果,显示如下图:
(3)
SELECT * FROM bb WHERE id IN (2,1,3,5,4) ORDER BY FIND_IN_SET(NAME,'AA,bb,cc,ee,dd') desc;
当在例(2)当加上find_in_set,并且按照name列的值给出的顺序(AA,bb,cc,ee,dd ),倒序排列的结果。显示如下图:
还有一个函数:substring_index,我以前的文章里有介绍它,作为截取字符串工具使用的,这里也可以用来做排序。
(1)
SELECT * FROM bb WHERE id IN (7,2,5,3,9,6) ;
默认执行出来的结果依旧是按照 id 升序排列,如下图:
(2)
SELECT * FROM bb WHERE id IN (7,2,5,3,9,6) ORDER BY SUBSTRING_INDEX('7,2,5,3,9,6',id,1) ;
加上substring_index后,从第一个字符开始,按照指定顺序显示结果集,如下图:
(3)
SELECT * FROM bb WHERE id IN (7,2,5,3,9,6) ORDER BY SUBSTRING_INDEX('7,2,5,3,9,6',id,1) DESC;
在例(2)基础上加上一个desc,就是按照指定顺序(7,2,5,3,9,6)的反顺序来显示结果集,如下图:
-----------------------------------------------------------------------------------------------------------------------------------------------
顺便,有个使用格式相近的函数,MAKE_SET也记上一笔:
make_set,用来选择自己需要的字符串,
用法(一):
make_set(n,'a','b','c','d','e'); n,为几,就选出第几个字符,
如:select make_set(2,'a','b','c','d','e'),则,结果显示为:b;
如:select make_set(5,'a','b','c','d','e'),则,结果显示为:e;
用法(二):
make_set(x|y,2,'a','b','c','d','e'),基本原理同用法(一),但是因为同时选择两个位子的字符,并且使用符号“|”,显示结果会将连个字符拼接到一起:
如:make_set(2|3,'a','b','c','d','e'),则,结果显示为:“bc”;
如:make_set(1|5,'a','b','c','d','e'),则,结果显示为:“ae”;