MySQL数据中每隔18行取5行

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

转载于:https://www.cnblogs.com/annofyf/p/9482262.html