COUNT/GROUP BY 有活动记录? [英] COUNT / GROUP BY with active record?

问题描述

我有一张包含以下信息的表格:

I have a table with the following info:

id  |  user_id  |  points
--------------------------
1   |  12       |  48
2   |  15       |  36
3   |  18       |  22
4   |  12       |  28
5   |  15       |  59
6   |  12       |  31

etc.

我想要的是前 10 个(数组)，每个 user_id 的条目最多(从高到低).所以使用上表我需要以下数组作为回报:

What I want is a top 10 (array) with most entries per user_id (order high to low). So using the table above I need the following array in return:

• 12 => 3 行
• 15 => 2 行
• 18 => 1 行
• 等

如何使用活动记录查询方法使用 CodeIgniter 执行此操作?这可以通过 COUNT 和 GROUP BY user_id 来完成吗?

How can I do this with CodeIgniter using the active record query method? Can this be done with COUNT and GROUP BY user_id?

推荐答案

我相信你会想要这样的:

I believe you'll want something like this:

 $this->db->select('user_id, COUNT(user_id) as total');
 $this->db->group_by('user_id'); 
 $this->db->order_by('total', 'desc'); 
 $this->db->get('tablename', 10);

这将产生类似

|  USER_ID |  TOTAL  |
|    12    |    3    |
|    15    |    2    |
|    18    |    1    |

更新:正如一些人在评论中指出的那样，原始查询是对 user_id 求和而不是对它们进行计数.我已更新活动记录查询以更正此问题.

UPDATE: As some pointed out in the comments the original query was summing the user_ids rather than counting them. I've updated the active record query to correct this.

这篇关于COUNT/GROUP BY 有活动记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！