MySQL怎么查询排序后结果集的前[N, M]条记录

今天群里有人提了这么一个问题，原话是：
“表T（a,b,c,d），怎么显示按c排序之后的21-30条记录。（a,b,c,d）中可能没有类似ID的属性”

我当时给出的答案是：
select * from T order by c where rownum in (21, 30);
不过后来想想可能这在MySQK上是不可行的。貌似只有Oracle支持吧。

先总结些流行数据库查询前N条记录的方法吧：
1、MySQL：
;这里取N为5
select * from T order by c limit 5;
2、SQL Server：
select top 5 * from T order by c;
3、Oracle：
select * from T order by c where rownum<5;

然后我针对MySQL给出了一条语句：
select A.* from T as A order by A.a limit 5 where A.a not in
(select B.a from T as B order by B.a limit 3);

这条语句在我现在这个版本的MySQL上执行不了，为什么呢？错误信息是：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/AN
Y/SOME subquery'

在MySQL4.1中子查询是不能使用LIMIT的，手册中也明确指明 This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

也就是说，这样的语句是不能正确执行的。
select * from table where id in (select id from table limit 10);

但是，只要你再来一层就行。。如：
select * from table where id in (select t.id from (select * from table limit 10)as t)

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的意思是，这版本的 MySQL 不支持使用 LIMIT 子句的 IN/ALL/ANY/SOME 子查詢，即是支持非 IN/ALL/ANY/SOME 子查詢的 LIMIT 子查詢。
歧义点在于各生字的 apply 和組合顺序，这句子的正确結合顺序是 “This version of MySQL doesn’t yet support ((LIMIT-(IN/ALL/ANY/SOME)) subquery)” （”-”符号为hyphen ），有可能的错误理解是 “This version of MySQL doesn’t yet support ((LIMIT subquery) & ((IN/ALL/ANY/SOME) subquery))”。

所以最后我给出的答案是：
select * from T as C where C.c in (select p.* from (select A.c from T as A order by A.c limit 30) as p)
and C.c not in
(select q.* from (select B.c from T as B order by B.c limit 21) as q);

虽然答案有点长，但是执行效率还是不算低的。如果大家有什么更好的方法，欢迎拍砖和指正。

更正：
shiwell给出了很简单的答案，我当时对Limit的用法不是很会，这里学习了，顺便感谢shinwell。
他给的答案是：
select * from T order by c LIMIT 21, 30

评论

5 楼 shansun123 2009-06-03  

zzxwill 写道

喜哥吧？你太有才了~

4 楼 zzxwill 2009-06-03  

楼下的楼下好像最简单！

3 楼 zzxwill 2009-06-03  

看了很受益~
下面是一些方法：

显示查询结果集的[M，N]结果
1． T（c），显示按c排序之后的21-30条记录
a) 方法一：子查询
mysql> select * from test
    -> ;
+-------+
| bonus |
+-------+
|     2 |
|  3000 |
|     0 |
|    80 |
|    50 |
|   150 |
+-------+
下面是加上“属性”rownum
mysql> select bonus, (select count(*) from test where t.bonus>=bonus ) as rownum
  from(select * from test order by bonus) t;
+-------+--------+
| bonus | rownum |
+-------+--------+
|     0 |      1 |
|     2 |      2 |
|    50 |      3 |
|    80 |      4 |
|   150 |      5 |
|  3000 |      6 |
+-------+--------+
6 rows in set (0.00 sec)

选出2到6条记录
mysql> select bonus from(select t.bonus, (select count(*) from test where t.bonu
s>=bonus ) as rownum  from(select * from test order by bonus) t) t2 where t2.row
num>=2 and t2.rownum<=5;
+-------+
| bonus |
+-------+
|     2 |
|    50 |
|    80 |
|   150 |
+-------+
4 rows in set (0.00 sec)

b) 方法二：ifnull函数
设置@x 初值：如
mysql> set @x=0;
Query OK, 0 rows affected (0.00 sec)
查询：
mysql>  select @x:=ifnull(@x,0)+1 as rownum , bonus from test order by bonus;
+--------+-------+
| rownum | bonus |
+--------+-------+
|      7 |     0 |
|      8 |     2 |
|      9 |    50 |
|     10 |    80 |
|     11 |   150 |
|     12 |  3000 |
+--------+-------+
6 rows in set (0.00 sec)

方法三：使用联接查询（笛卡尔积）
mysql> select a.bonus, count(*) as rownum  from test a,test b where a.bonus>=b.b
onus group by a.bonus;
+-------+--------+
| bonus | rownum |
+-------+--------+
|     0 |      1 |
|     2 |      2 |
|    50 |      3 |
|    80 |      4 |
|   150 |      5 |
|  3000 |      6 |
+-------+--------+
6 rows in set (0.00 sec)

2． T（c），显示21-30条记录
方法一：ifnull函数
mysql>  select @x:=ifnull(@x,0)+1 as rownum , bonus from test;
+--------+-------+
| rownum | bonus |
+--------+-------+
|      1 |     2 |
|      2 |  3000 |
|      3 |     0 |
|      4 |    80 |
|      5 |    50 |
|      6 |   150 |
+--------+-------+
6 rows in set (0.00 sec)

方法二：上面方法三

2 楼 shansun123 2009-06-03  

shinwell 写道

select * from T order by c LIMIT 21, 30

这个不行吗?

非常感谢您的留言，我试了，可以的。呵呵。

1 楼 shinwell 2009-06-03  

select * from T order by c LIMIT 21, 30

这个不行吗?