mysql> select * from (select @n:=@n+1 as n, a.* from (select * from result order by subjectno,studentresult desc)a,(select @n:=0)b)c where c.n%18<6 and c.n%18!=0;
result表定义如下：
mysql> desc result;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| StudentNo | int(4) | NO | | NULL | |
| SubjectNo | int(4) | NO | MUL | NULL | |
| ExamDate | datetime | NO | | NULL | |
| StudentResult | int(4) | NO | | NULL | |
+---------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
首先select @n:=@n+1 as n,a.* from (select * from result order by subjectno,studentresult desc) a, (select @n:=0)b给每行添加序号
结果如下：
mysql> select @n:=@n+1 as n,a.* from (select * from result order by subjectno,studentresult desc) a, (select @n:=0)b
-> ;
+------+-----------+-----------+---------------------+---------------+
| n | StudentNo | SubjectNo | ExamDate | StudentResult |
+------+-----------+-----------+---------------------+---------------+
| 1 | 1017 | 1 | 2013-11-11 16:00:00 | 100 |
| 2 | 1015 | 1 | 2013-11-11 16:00:00 | 99 |
| 3 | 1016 | 1 | 2013-11-11 16:00:00 | 97 |
| 4 | 1008 | 1 | 2013-11-11 16:00:00 | 96 |
| 5 | 1000 | 1 | 2013-11-11 16:00:00 | 94 |
| 6 | 1003 | 1 | 2013-11-11 16:00:00 | 91 |
| 7 | 1007 | 1 | 2013-11-11 16:00:00 | 87 |
| 8 | 1012 | 1 | 2013-11-11 16:00:00 | 86 |
| 9 | 1004 | 1 | 2013-11-11 16:00:00 | 84 |
| 10 | 1010 | 1 | 2013-11-11 16:00:00 | 83 |
| 11 | 1005 | 1 | 2013-11-11 16:00:00 | 82 |
| 12 | 1006 | 1 | 2013-11-11 16:00:00 | 82 |
| 13 | 1001 | 1 | 2013-11-11 16:00:00 | 76 |
| 14 | 1013 | 1 | 2013-11-11 16:00:00 | 73 |
| 15 | 1009 | 1 | 2013-11-11 16:00:00 | 67 |
| 16 | 1014 | 1 | 2013-11-11 16:00:00 | 64 |
| 17 | 1011 | 1 | 2013-11-11 16:00:00 | 62 |
| 18 | 1002 | 1 | 2013-11-11 16:00:00 | 61 |
| 19 | 1001 | 2 | 2012-11-10 10:00:00 | 93 |
最后序号对18取模值为1，2，3，4，5