问题:

I have the following table (points):

    recno   uid   uname   points
    ============================
    1       a     abc      10
    2       b     bac      8
    3       c     cvb      12
    4       d     aty      13
    5       f     cyu      9
    -------------------------
    --------------------------

What I need is to show only the top ten records with by points (desc) and five records on each page. I have following the SQL statement:

    select * from points where uid in(a,c) order by uid LIMIT 1, 5

Thanks

回答1:

for the first page:

SELECT * FROM points p ORDER BY points DESC LIMIT 0, 5

for the second page:

SELECT * FROM points p ORDER BY points DESC LIMIT 5, 5

回答2:

You can't execute an SQL query to return a set number of pages, you'll have to implement some kind of pagination module or whatever equivalent there is for the scenario you're in and fetch LIMIT 0, 5 for one then LIMIT 5, 5 for the other.

With such few records it wouldn't be an issue but in a production scale environment selected all records then breaking those results down into pages would be a lot of unnecessary overhead, it's good practice to only select the data you need.