LIMIT
限定查詢範圍
CREATE TABLE members (name VARCHAR(20));INSERT INTO members VALUES('Jagdish'),('Kenny'),('Rokurou'),('Immaculada');SELECT * FROM members;+------------+| name |+------------+| Jagdish || Kenny || Rokurou || Immaculada |+------------+
Select the first two names (no ordering specified):
SELECT * FROM members LIMIT 2;+---------+| name |+---------+| Jagdish || Kenny |+---------+
All the names in alphabetical order:
SELECT * FROM members ORDER BY name;+------------+| name |+------------+| Immaculada || Jagdish || Kenny || Rokurou |+------------+
The first two names, ordered alphabetically:
SELECT * FROM members ORDER BY name LIMIT 2;+------------+| name |+------------+| Immaculada || Jagdish |+------------+
The third name, ordered alphabetically (the first name would be offset zero, so the third is offset two):
SELECT * FROM members ORDER BY name LIMIT 2,1;+-------+| name |+-------+| Kenny |+-------+
From MariaDB 10.3.2, LIMIT can be used in a multi-table update:
CREATE TABLE warehouse (product_id INT, qty INT);INSERT INTO warehouse VALUES (1,100),(2,100),(3,100),(4,100);CREATE TABLE store (product_id INT, qty INT);INSERT INTO store VALUES (1,5),(2,5),(3,5),(4,5);UPDATE warehouse,store SET warehouse.qty = warehouse.qty-2, store.qty = store.qty+2 WHERE (warehouse.product_id = store.product_id AND store.product_id >= 1) ORDER BY store.product_id DESC LIMIT 2;SELECT * FROM warehouse;+------------+------+| product_id | qty |+------------+------+| 1 | 100 || 2 | 100 || 3 | 98 || 4 | 98 |+------------+------+SELECT * FROM store;+------------+------+| product_id | qty |+------------+------+| 1 | 5 || 2 | 5 || 3 | 7 || 4 | 7 |+------------+------+
From MariaDB 10.3.3, LIMIT can be used with GROUP_CONCAT, so, for example, given the following table:
CREATE TABLE d (dd DATE, cc INT);INSERT INTO d VALUES ('2017-01-01',1);INSERT INTO d VALUES ('2017-01-02',2);INSERT INTO d VALUES ('2017-01-04',3);
the following query:
SELECT SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) FROM d;+----------------------------------------------------------------------------+| SUBSTRING_INDEX(GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC),",",1) |+----------------------------------------------------------------------------+| 2017-01-04:3 |+----------------------------------------------------------------------------+
can be more simply rewritten as:
SELECT GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) FROM d;+-------------------------------------------------------------+| GROUP_CONCAT(CONCAT_WS(":",dd,cc) ORDER BY cc DESC LIMIT 1) |+-------------------------------------------------------------+| 2017-01-04:3 |+-------------------------------------------------------------+