SQL實現分組limit
mysql> create table a (id int,fid int ,name varchar(10));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into a value (1,1,'a');
Query OK, 1 row affected (0.04 sec)
mysql> insert into a value (2,1,'b');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (3,1,'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (4,2,'d');
Query OK, 1 row affected (0.02 sec)
mysql> insert into a value (5,2,'e');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (6,2,'f');
Query OK, 1 row affected (0.00 sec)
+------+------+------+
| id | fid | name |
+------+------+------+
| 2 | 1 | b |
| 3 | 1 | c |
| 5 | 2 | e |
| 6 | 2 | f |
+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from a where 2>( select count(*) from a b where a.fid=b.fid and a.id>b.id )order by fid ;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 4 | 2 | d |
| 5 | 2 | e |
+------+------+------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.10 sec)
mysql> insert into a value (1,1,'a');
Query OK, 1 row affected (0.04 sec)
mysql> insert into a value (2,1,'b');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (3,1,'c');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (4,2,'d');
Query OK, 1 row affected (0.02 sec)
mysql> insert into a value (5,2,'e');
Query OK, 1 row affected (0.01 sec)
mysql> insert into a value (6,2,'f');
Query OK, 1 row affected (0.00 sec)
mysql> select * from a
-> ;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 1 | c |
| 4 | 2 | d |
| 5 | 2 | e |
| 6 | 2 | f |
+------+------+------+
6 rows in set (0.00 sec)
mysql> select * from a where 2>( select count(*) from a b where a.fid=b.fid and a.id<b.id )order by fid ;-> ;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 3 | 1 | c |
| 4 | 2 | d |
| 5 | 2 | e |
| 6 | 2 | f |
+------+------+------+
6 rows in set (0.00 sec)
+------+------+------+
| id | fid | name |
+------+------+------+
| 2 | 1 | b |
| 3 | 1 | c |
| 5 | 2 | e |
| 6 | 2 | f |
+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from a where 2>( select count(*) from a b where a.fid=b.fid and a.id>b.id )order by fid ;
+------+------+------+
| id | fid | name |
+------+------+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 4 | 2 | d |
| 5 | 2 | e |
+------+------+------+
4 rows in set (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28282660/viewspace-2133644/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server--實現 Limit m, n 的功能SQLServerMIT
- SQL優化:limit分頁優化SQL優化MIT
- SQL分組SQL
- Sql字串分組Split函式的兩種實現方法SQL字串函式
- MySQL實現分組排序MySql排序
- 如何在SQL Server中實現 Limit m,n 的功能SQLServerMIT
- oracle實現"limit"功能OracleMIT
- sql分組查詢語句--行內分組(非聚合分組)SQL
- SQL 分組排序group bySQL排序
- mysql實戰--MYSQL中的SQL分組方法MySql
- Java利用Comparator實現分組排序Java排序
- mysql分頁-limit offset分頁MySqlMIT
- [SQL Server]分頁功能的實現SQLServer
- sql server分組查詢示例SQLServer
- MongoDB 如何實現巢狀子文件分組MongoDB巢狀
- mysql 使用技巧 分頁limitMySqlMIT
- SQL優化之limit 1SQL優化MIT
- SQL之limit子句的使用SQLMIT
- sql中limit使用方法SQLMIT
- 業務SQL那些事–慎用LIMITSQLMIT
- SQL 如何查詢每個分組都出現的欄位值SQL
- Mysql 分組排序的sql寫法MySql排序
- 偷樑換柱 - iOS實現UITextField+LimitiOSUIMIT
- Access查詢實現Mysql的 limit 查詢MySqlMIT
- 【Gin-API系列】實現動態路由分組(七)API路由
- 如何實現iOS6的TableView分組樣式iOSView
- 用java語言用sort方法實現groupby分組Java
- 使用SQL按照區間進行分組SQL
- Oracle常見SQL分頁實現方案介紹OracleSQL
- oracle資料庫用sql實現快速分頁Oracle資料庫SQL
- 如何在分組報表中實現組內資料補空行及組內頁碼
- Oracle分頁(limit方式的運用)OracleMIT
- BIRT 中根據引數實現動態日期分組
- Python正規表示式實現非捕獲分組Python
- 用listagg函式分組實現列轉行函式
- sql改寫優化:簡單規則重組實現SQL優化
- sql 按著時間分組,每組取 20 條資料SQL
- SQL根據分組求連續範圍SQL