MySQL分组与左联接

本文介绍了MySQL分组与左联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我正在尝试做一个非常复杂的查询(至少对我来说是非常复杂的，而不是对您来说:))

我有用户和评论表.

SQL提琴手: sqlfiddle/#!9/b1f845/2

select user_id, status_id from comments where user_id in (2,3); +---------+-----------+ | user_id | status_id | +---------+-----------+ | 2 | 10 | | 2 | 10 | | 2 | 10 | | 2 | 7 | | 2 | 7 | | 2 | 10 | | 3 | 9 | | 2 | 9 | | 2 | 6 | +---------+-----------+

如果我使用

select user_id, status_id from comments where user_id in (2,3)

它返回很多重复的值.

如果可能的话，我想得到什么.

如果看到status_id = 10具有user_id= 2,3 and 4 and 2 multiple times. 因此，从这里我想获得最大的最新user_id(唯一值)，例如

现在它将是user_id = 4 and 2的主要复杂部分.我现在想在一列中获取user_id= 4 and 2的用户信息，以便最后我可以得到这样的信息

我怎么能实现这么复杂的东西. 目前，我必须使用应用程序逻辑来做到这一点.

谢谢您的时间.

解决方案

我认为这可能是您真正想要的:

SELECT DISTINCT status_id, (SELECT MAX(user_id) FROM comments c2 WHERE c1.status_id = c2.status_id) user_1, (SELECT user_id FROM comments c2 WHERE c1.status_id = c2.status_id ORDER BY user_id LIMIT 1 OFFSET 1) user_2 FROM comments c1 WHERE user_id IN (2,3);

演示 (您的更新小提琴)我们可以使用相关子查询为每个status_id查找最大user_id和第二至最大user_id，然后将每个旋转为两个单独的列.在这里最好使用GROUP_CONCAT方法，因为它还使您可以轻松地将任意数量的用户容纳为CSV列表.

此外，如果您使用的是MySQL 8+或更高版本，那么我们可以利用等级分析功能，这也将更加容易.

I am trying to do a very complex query (at least extremely complex for me not for YOU :) )

I have users and comments table.

SQL Fiddle: sqlfiddle/#!9/b1f845/2

select user_id, status_id from comments where user_id in (2,3); +---------+-----------+ | user_id | status_id | +---------+-----------+ | 2 | 10 | | 2 | 10 | | 2 | 10 | | 2 | 7 | | 2 | 7 | | 2 | 10 | | 3 | 9 | | 2 | 9 | | 2 | 6 | +---------+-----------+

If I use

select user_id, status_id from comments where user_id in (2,3)

It returns a lot of duplicate values.

What I want to get if possible.

If you see status_id = 10 has user_id= 2,3 and 4 and 2 multiple times. So from here I want to get maximum of latest user_id (unique) so for example,

it will be user_id = 4 and 2 now the main complex part. I now want to get users information of user_id= 4 and 2 in one column so that at the end I can get something like this

How can I achieve such a complex things. Currently I have to do it using application logic.

Thank you for your time.

解决方案

I think this might be what you literally want here:

SELECT DISTINCT status_id, (SELECT MAX(user_id) FROM comments c2 WHERE c1.status_id = c2.status_id) user_1, (SELECT user_id FROM comments c2 WHERE c1.status_id = c2.status_id ORDER BY user_id LIMIT 1 OFFSET 1) user_2 FROM comments c1 WHERE user_id IN (2,3);

Demo (your update Fiddle)

We can use correlated subqueries to find the max user_id and second-to-max user_id for each status_id, and then spin each of those out as two separate columns. Using a GROUP_CONCAT approach might be preferable here, since it would also allow you to easily accommodate any numbers of users as a CSV list.

Also, if you were using MySQL 8+ or greater, then we could take advantage of the rank analytic functions, which would also be easier.

更多推荐

MySQL分组与左联接