MySQL分组与左联接 [英] MySQL group by with left join
问题描述
我正在尝试做一个非常复杂的查询(至少对我来说是非常复杂的,而不是对您来说:))
我有用户和评论表.
SQL提琴手: http://sqlfiddle.com/#!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
的用户信息,以便最后我可以得到这样的信息
status_id | userOneUserName | userTwoUserName
10 sadek4 iamsadek2
---------------------------------------------
7 | iamsadek2 | null
---------------------------------------------
9 . | iamsadek2 | sadek2
---------------------------------------------
6 | iamsadek2 | null
我怎么能实现这么复杂的东西. 目前,我必须使用应用程序逻辑来做到这一点.
谢谢您的时间.
我认为这可能是您真正想要的:
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: http://sqlfiddle.com/#!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
status_id | userOneUserName | userTwoUserName
10 sadek4 iamsadek2
---------------------------------------------
7 | iamsadek2 | null
---------------------------------------------
9 . | iamsadek2 | sadek2
---------------------------------------------
6 | iamsadek2 | null
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分组与左联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!