MySQL LIMIT子句

语法：

SELECT   select_expr [, select_expr ...] 
   FROM  table_name 
   [WHERE where_condition] 
   [GROUP BY {col_name | expr} [ASC | DESC], ... [WITH ROLLUP]]
  [HAVING where_condition]
  [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
  [LIMIT {[offset,] row_count | row_count OFFSET offset}]

MySQL特有的子句。它是SELECT语句中的最后一个子句（在order by后面）。它用来表示从结果集中选取最前面或最后面的几行。偏移量offset的最小值为0。
语法：limit  <获取的行数> [OFFSET <跳过的行数>]
或者  limit [<跳过的行数>,] <获取的行数>  

例： 得到编号最大的前4个球员的编号和名字

root@TENNIS 16:07  mysql>SELECT  playerno, name
    ->   FROM  PLAYERS
    ->   ORDER BY  playerno DESC
    ->   LIMIT  4;
+----------+-----------+
| playerno | name      |
+----------+-----------+
|      100 | Parmenter |
|       95 | Miller    |
|       83 | Hope      |
|       44 | Baker     |
+----------+-----------+
4 rows in set (0.00 sec)

行先按照球员编号进行降序排序，然后使用limit从中挑出最前面的4行。如果没有order by子句，返回的4行就是不可预料的。

当有几个相等的值在最前面时，返回什么？
例： 得到最优秀的前3个球员的编号。赢得比赛数目最多的球员是最优秀的

root@TENNIS 16:09  mysql>SELECT  playerno ,count(*) AS number
    ->   FROM  MATCHES
    ->   WHERE  won > lost
    ->   GROUP BY  playerno
    ->   ORDER BY  number DESC
    ->   LIMIT  3;
+----------+--------+
| playerno | number |
+----------+--------+
|        6 |      2 |
|       44 |      1 |
|       57 |      1 |
+----------+--------+
3 rows in set (0.00 sec)

语句中没有指明当出现相等值时应该怎么办，因此mysql随机返回57和44球员。

例： 得到最优秀的前3个球员的编号

root@TENNIS 16:15  mysql>SELECT playerno ,count(*) AS number
    ->   FROM MATCHES
    ->   WHERE won > lost
    ->   GROUP BY playerno
    ->   ORDER BY number DESC, playerno DESC
    ->   LIMIT 3;
+----------+--------+
| playerno | number |
+----------+--------+
|        6 |      2 |
|      104 |      1 |
|       57 |      1 |
+----------+--------+
3 rows in set (0.01 sec)

如果球员具有相同的获胜次数，那么只显示编号最大的球员。

带偏移量的limit

用来跳过前面的几行后再取
例：得到球员编号最低的5个球员的编号和名字，从第4个球员开始

root@TENNIS 16:10  mysql>SELECT playerno,  name
    ->   FROM PLAYERS
    ->   ORDER BY playerno ASC
    ->   LIMIT  3, 5;  -- 或者limit 5 offset 3;
+----------+-----------+
| playerno | name      |
+----------+-----------+
|        8 | Newcastle |
|       39 | Bishop    |
|       44 | Baker     |
|       83 | Hope      |
|       95 | Miller    |
+----------+-----------+
5 rows in set (0.00 sec)

练习

1. 4个最低的罚款额的平均值是多少？

root@TENNIS 16:12  mysql>SELECT avg(amount)
    ->   FROM (SELECT amount
    ->           FROM PENALTIES
    ->       ORDER BY amount
    ->          LIMIT 4 ) a;
+-------------+
| avg(amount) |
+-------------+
|   32.500000 |
+-------------+
1 row in set (0.00 sec)

子查询得到最低的4个罚款额。外查询对其求平均值

2. 第3高的罚款额是多少？

root@TENNIS 16:13  mysql>SELECT min(amount)
    ->   FROM (SELECT  DISTINCT  amount
    ->           FROM PENALTIES
    ->       ORDER BY amount DESC
    ->        LIMIT 3 ) a;
+-------------+
| min(amount) |
+-------------+
|       50.00 |
+-------------+
1 row in set (0.00 sec)

root@TENNIS 16:15  mysql>SELECT  DISTINCT  amount           FROM PENALTIES       ORDER BY amount DESC        LIMIT 2,1;
+--------+
| amount |
+--------+
|  50.00 |
+--------+
1 row in set (0.00 sec)

子查询得到最高的3个罚款额。外查询从中选一个最小的