mysql 获取 order by 发生在 group by 之前

发布于 2024-10-01 08:34:26 字数 509 浏览 0 评论 0原文

我有三个表，我将在这里向您展示相关列

表：组列：group_id、名称。
表：groups_to_message 列： group_id、message_id
表：消息列：message_id，创建（日期）

我基本上需要找到每个组的最后一条消息，而不显示重复的组。

我尝试过像这样使用 group by ：

SELECT m.created, g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m 
GROUP BY g.group_id 
ORDER BY m.created DESC

这会导致成功分组，但在 ORDER BY 之前完成，因此第一个结果在排序之前获取。

任何帮助表示赞赏。

原文

I have three tables and I will just show you the relevant columns here

Table: groups Columns: group_id, name.
Table: groups_to_message Columns:
group_id, message_id
Table: messages Columns: message_id,
created (date)

I need to basically find the last message for each group, with out showing duplicate groups.

I've tried using group by like this:

SELECT m.created, g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m 
GROUP BY g.group_id 
ORDER BY m.created DESC

This causes the successful grouping but is done before the ORDER BY so the first result is taken before the sort.

Any help appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

乖乖兔^ω^ 2024-10-08 08:34:26

如果我没看错你的问题，请使用 MAX()。这应该为您提供每个组的最新项目

示例...

SELECT MAX(m.created), g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m ON (gm.message_id = gm.message_id)
GROUP BY g.group_id 
ORDER BY m.created DESC

If I'm reading your question right, Use MAX(). This should get you the most recent item for each group

Example...

SELECT MAX(m.created), g.group_id 
FROM groupss as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m ON (gm.message_id = gm.message_id)
GROUP BY g.group_id 
ORDER BY m.created DESC

回复收藏 0 原文

情深如许 2024-10-08 08:34:26

这未经测试，但应该有效：

SELECT m.created, g.group_id 
FROM groups as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m ON (gm.message_id = m.id)
GROUP BY g.group_id HAVING m.created = MAX(m.created)

This is untested, but it should work:

SELECT m.created, g.group_id 
FROM groups as g 
JOIN group_to_message as gm ON (g.group_id = gm.group_id) 
JOIN messages as m ON (gm.message_id = m.id)
GROUP BY g.group_id HAVING m.created = MAX(m.created)

回复收藏 0 原文

烟─花易冷 2024-10-08 08:34:26

SELECT  m.created, g.group_id
FROM    groupss g
JOIN    messages m
ON      m.id = 
        (
        SELECT  mi.id
        FROM    group_to_message gm
        JOIN    message mi
        ON      mi.message_id = gm.message_id
        WHERE   gm.group_id = g.group_id
        ORDER BY
                gm.group_id DESC, mi.created DESC
        LIMIT 1
        )

SELECT  m.created, g.group_id
FROM    groupss g
JOIN    messages m
ON      m.id = 
        (
        SELECT  mi.id
        FROM    group_to_message gm
        JOIN    message mi
        ON      mi.message_id = gm.message_id
        WHERE   gm.group_id = g.group_id
        ORDER BY
                gm.group_id DESC, mi.created DESC
        LIMIT 1
        )

回复收藏 0 原文

~没有更多了~