如何计算mysql查询中的行数？

Question

我想按行号查询mysql？假设我想查询最后10行，如何在mysql查询中实现？

这是我的mysql查询，它是按6天间隔查询的，但我需要按最近10行查询，而不是按6天间隔查询。

SELECT people_id, people_number 'people number', count(people_number) 'Occurs',
  (
   Select count(people_number) from people_history
   where people_id =  5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
  ) 'Total',
  count(people_number)/(
      Select count(people_number) from people_history
      where people_id = 5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
  )*100 'Percent'
FROM people_history
where people_id = 5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
group by people_id, people_number
order by Percent desc`

Answer 1

将您的查询设置为：

SELECT * FROM people_history ORDER BY id DESC LIMIT 10

Limit允许您限制从查询中获得的结果数量。通过按ID排序，您可以获得表中的最后20个结果。

Answer 2

LIMIT

SELECT 
    people_id,
    people_number 'people number',
    count(people_number) 'Occurs',
    (Select 
        count(people_number) 
    from 
        people_history 
    where 
        people_id = 5486 
        and date > DATE_SUB(NOW(), INTERVAL 6 DAY)) 'Total',            
    count(people_number) / 
        (Select 
            count(people_number) 
        from 
            people_history 
        where 
            people_id = 5486 
            and date > DATE_SUB(NOW(), INTERVAL 6 DAY)) *100 'Percent' 
FROM 
    people_history 
WHERE
    people_id = 5486 
    and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
GROUP BY
    people_id, people_number 
ORDER BY 
    Percent desc
LIMIT 0, 10

并且您可能希望稍微重写一下该查询。

Answer 3

颠倒顺序，最后10个是前10个，限制是10个。