SQL - ORDER BY - 线程留言 [英] SQL - ORDER BY - Threads & Messages

问题描述

我有三张桌子:

Message_Threads: ( thread_ID )
Message_Users:   ( thread_ID, user_ID, notify )
Messages:        ( thread_ID, user_ID, message_ID, date_created )

我试图仅按照线程中最新消息的顺序列出线程，但我想显示那些在其余消息上方具有 notify=1 的线程(对于线程上的所有其他用户，notify 设置为 1当发布新消息并在他们访问该线程时设置为 0).有人给我一个怪物选择语句吗?

I am trying to list out just the threads in order of most recent message within the thread, but I want to show the ones that have notify=1 above the rest (notify is set to 1 for all other users on a thread when a new message is posted and set to 0 when they visit that thread). Anyone got a monster select statement for me?

推荐答案

你的数据结构没有明确消息和消息用户的关系.我的假设是这些都加入了 thread_id 和 user_id.然后在线程级别聚合它们:

Your data structure does not make clear the relationship be messages and message user. My assumption is that these are joined on both thread_id and user_id. This then aggregates them at the thread level:

select t.thread_id
from thread t join
     message m
     on t.thread_id = m.thread_id join
     message_users mu
     on m.thread_id = mu.thread_id and
        m.user_id = mu.user_id
group by thread_id
order by max(mu.notify) desc, max(m.date_created) desc

关键是最后的订单.首先按通知排序，但降序使1"出现在0"之前(假设非 1 值为 0).然后按创建日期.

The key to this is the final order by. Order by notify first, but descending so "1" appears before "0" (assuming non-1 value is 0). Then by date created.

要将其限制为特定用户，请在 group by 前使用 WHERE 语句:

To restrict this to a particular user, use a WHERE statement before the group by:

WHERE u.user_id = session_user_id

这篇关于SQL - ORDER BY - 线程留言的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！