MYSQL查詢開頭或最後幾行
mysql 查詢開頭或最後幾行(Limit)
from http://hi.baidu.com/sunboy_2050/item/ccda0eceddd9a42de80f2ef0
在我們使用查詢語句的時候,經常要傳回前幾條或者中間某幾行資料
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
LIMIT 子句可以被用于強制 SELECT 語句傳回指定的記錄數。
LIMIT 接受一個或兩個數字參數。參數必須是一個整數常量。
如果給定兩個參數,第一個參數指定第一個傳回記錄行的偏移量,第二個參數指定傳回記錄行的最大數目。
初始記錄行的偏移量是 0(而不是 1): 為了與 PostgreSQL 相容,MySQL 也支援句法: LIMIT # OFFSET #。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
mysql> SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
//為了檢索從某一個偏移量到記錄集的結束所有的記錄行,可以指定第二個參數為 -1:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
mysql> SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
//如果隻給定一個參數,它表示傳回最大的記錄行數目:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
mysql> SELECT * FROM table LIMIT 5; //檢索前 5 個記錄行
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
//換句話說,LIMIT n 等價于 LIMIT 0,n。
示例:
select top, id, dtime from gametop800 where top=1 order by dtime limit 0, 10;
查詢後n行記錄
select * from table order by id desc limit n;//倒序排序(desc)
查詢一條記錄($id)的下一條記錄
select * from table where id>$id order by id asc limit 1
查詢一條記錄($id)的上一條記錄
select * from table where id<$id order by id desc limit 1
示例:
select top, id, dtime from gametop800 where top=1 order by dtimedesclimit 0, 10;
上面查詢最後n行記錄,雖然可以通過desc倒排實作,但最終的結果集也是倒排的
要使desc倒排後,結果集依然能夠哦asc升序排列,我們可以借助資料庫子表查詢實作
即在子表的查詢條件中,重新把desc結果進行asc排列
示例:
select * from (select top, id, dtime from gametop800 where top=1 order by dtimedesclimit 0, 10) as tbl order by dtime asc;
最後,附上一個案例
MYSQL查詢一周内的資料(最近7天的)怎麼寫
select * from wap_content where week(created_at) = week(now)
如果你要嚴格要求是某一年的,那可以這樣
查詢一天:
select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate();
查詢一周:
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);
查詢一個月:
select * from table where DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column_time);
#Sql