mysql 查询商品分类下的n条数据库_MySQL怎么查询排序后结果集的前[N, M]条记录

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)

方法二：上面方法三