推荐的方法在计数时在mysql中进行分页
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了推荐的方法在计数时在mysql中进行分页,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5296字,纯文字阅读大概需要8分钟。
内容图文
我有一个像这样的MySQl建议表
id:int
user_id:int
item_id:int
我想为用户提供最多建议项目的分页.假设每个页面大小为9.所以我尝试过
select count(id) as total,item_id from suggestions group by item_id order by total desc limit 9;
它只适用于第一页并返回
+-------+---------+
| total | item_id |
+-------+---------+
| 6 | 4 |
| 6 | 8 |
| 6 | 16 |
| 5 | 5 |
| 5 | 6 |
| 5 | 1 |
| 3 | 17 |
| 2 | 3 |
| 2 | 7 |
+-------+---------+
在第二页中,我想显示total = 2和item_id = 2的结果.在SQL服务器中,我可以使用Row_number()方法对分页行进行编号.但我在MySQL中找不到这样的东西.
我是mysql的新手,有人可以帮帮我.
请参阅下面附带的示例数据
+----+---------+---------+---------------------+---------------------+
| id | user_id | item_id | created_at | updated_at |
+----+---------+---------+---------------------+---------------------+
| 1 | 4 | 1 | 2011-04-18 05:21:08 | 2011-04-18 05:21:08 |
| 6 | 5 | 2 | 2011-04-18 07:28:25 | 2011-04-18 07:28:25 |
| 8 | 5 | 3 | 2011-04-18 08:10:46 | 2011-04-18 08:10:46 |
| 9 | 4 | 4 | 2011-04-18 12:58:17 | 2011-04-18 12:58:17 |
| 10 | 4 | 5 | 2011-04-18 13:24:59 | 2011-04-18 13:24:59 |
| 11 | 4 | 6 | 2011-04-18 13:28:28 | 2011-04-18 13:28:28 |
| 12 | 4 | 7 | 2011-04-18 13:30:20 | 2011-04-18 13:30:20 |
| 13 | 8 | 5 | 2011-04-18 13:42:30 | 2011-04-18 13:42:30 |
| 14 | 8 | 6 | 2011-04-18 13:42:44 | 2011-04-18 13:42:44 |
| 15 | 8 | 4 | 2011-04-18 13:42:52 | 2011-04-18 13:42:52 |
| 16 | 9 | 5 | 2011-04-18 13:44:34 | 2011-04-18 13:44:34 |
| 17 | 9 | 6 | 2011-04-18 13:45:23 | 2011-04-18 13:45:23 |
| 18 | 9 | 7 | 2011-04-18 13:46:28 | 2011-04-18 13:46:28 |
| 19 | 9 | 1 | 2011-04-18 13:46:29 | 2011-04-18 13:46:29 |
| 20 | 4 | 8 | 2011-04-18 13:58:38 | 2011-04-18 13:58:38 |
| 21 | 8 | 8 | 2011-04-18 13:59:39 | 2011-04-18 13:59:39 |
| 22 | 9 | 8 | 2011-04-18 14:00:47 | 2011-04-18 14:00:47 |
| 23 | 8 | 1 | 2011-04-18 14:03:58 | 2011-04-18 14:03:58 |
| 24 | 4 | 9 | 2011-04-19 10:24:38 | 2011-04-19 10:24:38 |
| 25 | 4 | 10 | 2011-04-19 10:54:13 | 2011-04-19 10:54:13 |
| 26 | 4 | 11 | 2011-04-19 10:57:10 | 2011-04-19 10:57:10 |
| 27 | 12 | 8 | 2011-04-19 11:33:02 | 2011-04-19 11:33:02 |
| 28 | 12 | 4 | 2011-04-19 11:33:18 | 2011-04-19 11:33:18 |
| 29 | 12 | 1 | 2011-04-19 12:07:51 | 2011-04-19 12:07:51 |
| 30 | 12 | 6 | 2011-04-19 12:09:05 | 2011-04-19 12:09:05 |
| 31 | 12 | 12 | 2011-04-19 13:07:35 | 2011-04-19 13:07:35 |
| 32 | 9 | 13 | 2011-04-19 13:29:17 | 2011-04-19 13:29:17 |
| 33 | 9 | 14 | 2011-04-19 13:45:56 | 2011-04-19 13:45:56 |
| 34 | 9 | 15 | 2011-04-19 13:49:08 | 2011-04-19 13:49:08 |
| 35 | 14 | 16 | 2011-04-19 16:47:28 | 2011-04-19 16:47:28 |
| 36 | 4 | 16 | 2011-04-19 16:49:46 | 2011-04-19 16:49:46 |
| 37 | 13 | 16 | 2011-04-19 16:50:29 | 2011-04-19 16:50:29 |
| 38 | 15 | 5 | 2011-04-19 16:53:55 | 2011-04-19 16:53:55 |
| 39 | 15 | 16 | 2011-04-19 16:54:15 | 2011-04-19 16:54:15 |
| 40 | 15 | 8 | 2011-04-19 16:54:29 | 2011-04-19 16:54:29 |
| 41 | 15 | 4 | 2011-04-19 16:58:20 | 2011-04-19 16:58:20 |
| 42 | 15 | 0 | 2011-04-19 16:59:21 | 2011-04-19 16:59:21 |
| 44 | 9 | 4 | 2011-04-19 17:03:54 | 2011-04-19 17:03:54 |
| 45 | 9 | 16 | 2011-04-19 17:03:57 | 2011-04-19 17:03:57 |
| 46 | 13 | 8 | 2011-04-19 17:08:56 | 2011-04-19 17:08:56 |
| 47 | 13 | 6 | 2011-04-19 17:08:58 | 2011-04-19 17:08:58 |
| 48 | 13 | 17 | 2011-04-19 17:10:26 | 2011-04-19 17:10:26 |
| 49 | 18 | 16 | 2011-04-19 17:15:49 | 2011-04-19 17:15:49 |
| 50 | 18 | 4 | 2011-04-19 17:15:52 | 2011-04-19 17:15:52 |
| 51 | 18 | 1 | 2011-04-19 17:15:53 | 2011-04-19 17:15:53 |
| 52 | 18 | 5 | 2011-04-19 17:15:54 | 2011-04-19 17:15:54 |
| 53 | 18 | 17 | 2011-04-19 17:15:57 | 2011-04-19 17:15:57 |
| 54 | 19 | 17 | 2011-04-19 17:21:45 | 2011-04-19 17:21:45 |
| 55 | 19 | 18 | 2011-04-19 17:22:28 | 2011-04-19 17:22:28 |
| 56 | 22 | 19 | 2011-04-19 17:35:40 | 2011-04-19 17:35:40 |
| 57 | 26 | 20 | 2011-04-19 17:46:00 | 2011-04-19 17:46:00 |
| 58 | 26 | 21 | 2011-04-19 17:51:51 | 2011-04-19 17:51:51 |
| 59 | 26 | 22 | 2011-04-19 17:54:32 | 2011-04-19 17:54:32 |
| 60 | 26 | 23 | 2011-04-19 18:11:24 | 2011-04-19 18:11:24 |
| 61 | 26 | 24 | 2011-04-19 18:12:23 | 2011-04-19 18:12:23 |
| 62 | 27 | 2 | 2011-04-19 18:24:38 | 2011-04-19 18:24:38 |
| 63 | 27 | 25 | 2011-04-19 18:29:07 | 2011-04-19 18:29:07 |
| 64 | 27 | 26 | 2011-04-19 18:31:51 | 2011-04-19 18:31:51 |
| 65 | 27 | 3 | 2011-04-19 18:32:46 | 2011-04-19 18:32:46 |
+----+---------+---------+---------------------+---------------------+
解决方法:
你有没有尝试过:
select count(id) as total,item_id from suggestions
group by item_id order by total desc
limit 9 offset 9;
内容总结
以上是互联网集市为您收集整理的推荐的方法在计数时在mysql中进行分页全部内容,希望文章能够帮你解决推荐的方法在计数时在mysql中进行分页所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。