首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >按列将项目分组,按其他列按顺序分组

问按列将项目分组,按其他列按顺序分组
EN

Stack Overflow用户
提问于 2015-04-17 06:42:10
回答 3查看 29关注 0票数 2

我有如下表,我想采取客户端的最新评级-基本上是用户-随时更新评等,计数将增加,表中将有一个条目。表如下所示

代码语言:javascript
复制
-----------------------------------------------------
|_id| name   | client_id | user_id | rating | count |
-----------------------------------------------------
|1  | Four   |   1       |    1    |   4    |   1   |
|2  | three  |   1       |    1    |   3    |   2   |
|3  | two    |   1       |    1    |   2    |   3   |
|4  | five   |   1       |    1    |   5    |   4   |
|5  | two    |   1       |    2    |   2    |   1   |
|6  | three  |   1       |    2    |   3    |   2   |
|7  | two    |   2       |    1    |   2    |   1   |
|8  | three  |   2       |    1    |   3    |   2   |
-----------------------------------------------------

对于client_id 1的评级,我希望输出如下

代码语言:javascript
复制
-----------------------------------------------------
|_id| name   | client_id | user_id | rating | count |
-----------------------------------------------------
|4  | five   |   1       |    1    |   5    |   4   |
|6  | three  |   1       |    2    |   3    |   2   |
-----------------------------------------------------

到目前为止,我尝试了SELECT * FROM test where client_id = 1 group by client_id order by count desc;,但没有得到预期的结果,有帮助吗??

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2015-04-17 06:58:51

您可以在同一表上使用left join

代码语言:javascript
复制
select t1.* from test t1 
left join test t2 on t1.user_id = t2.user_id 
and t1.client_id = t2.client_id 
and t1._id < t2._id 
where 
t2._id is null 
and t1.client_id = 1 
order by t1.`count` desc;

使用不相关的子查询,您可以这样做

代码语言:javascript
复制
select t1.* from test t1 
join ( 
  select max(_id) as _id,
  client_id,
  user_id 
  from test 
  where client_id = 1 
  group by client_id,user_id 
)t2 
on t1._id = t2._id 
and t1.client_id = t2.client_id 
order by t1.`count` desc; 

更新:如何将另一个表连接到上面的注释中,下面是一个示例

代码语言:javascript
复制
mysql> select * from users ;
+------+------+
| _id  | name |
+------+------+
|    1 | AAA  |
|    2 | BBB  |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test ;
+------+-------+-----------+---------+--------+-------+
| _id  | name  | client_id | user_id | rating | count |
+------+-------+-----------+---------+--------+-------+
|    1 | four  |         1 |       1 |      4 |     1 |
|    2 | three |         1 |       1 |      3 |     2 |
|    3 | two   |         1 |       1 |      2 |     3 |
|    4 | five  |         1 |       1 |      5 |     4 |
|    5 | two   |         1 |       2 |      2 |     1 |
|    6 | three |         1 |       2 |      3 |     2 |
|    7 | two   |         2 |       1 |      2 |     1 |
|    8 | three |         2 |       1 |      3 |     2 |
+------+-------+-----------+---------+--------+-------+

select t1.*,u.name from test t1 
join users u on u._id = t1.user_id
left join test t2 on t1.user_id = t2.user_id 
and t1.client_id = t2.client_id 
and t1._id < t2._id 
where 
t2._id is null 
and t1.client_id = 1 
order by t1.`count` desc;

会给你

代码语言:javascript
复制
+------+-------+-----------+---------+--------+-------+------+
| _id  | name  | client_id | user_id | rating | count | name |
+------+-------+-----------+---------+--------+-------+------+
|    4 | five  |         1 |       1 |      5 |     4 | AAA  |
|    6 | three |         1 |       2 |      3 |     2 | BBB  |
+------+-------+-----------+---------+--------+-------+------+

请注意,连接到users表是内部连接,这将要求在users表( test表)中预置所有用户。

如果users表中缺少一些用户,那么使用left join,对于从users表中选择的数据,这将具有null值。

票数 2
EN

Stack Overflow用户

发布于 2015-04-17 06:47:15

你可以尝试这样的方法

代码语言:javascript
复制
select _id, name, client_id, user_id, rating, max(count)
from clients 
group by client_id
票数 0
EN

Stack Overflow用户

发布于 2015-04-17 06:53:23

试试看

代码语言:javascript
复制
SELECT * FROM test
where client_id = 1 
group by user_id 
order by count desc
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/29692075

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档