mysql - 如何按字段分组然后按不同字段排序然后限制

Question

我有一个查询，例如:

SELECT _threads.*, _messages.*
FROM _threads
LEFT JOIN _messages
ON _threads.thread_id = _messages.message_thread_id
WHERE _threads.thread_servicer_id = 'jG5s2b6TRs'
GROUP BY _threads.thread_customer_id

返回类似以下内容:

如何修改查询以便能够按 thread_customer_id 分组，然后按 message_time 排序，并且仅选择具有最新 message_time 的行.

Answer 1

预查询按客户分组的线程/消息，并获取每个服务商 ID 过滤器的最大发布时间。由此，它将返回单个客户/日期时间。然后，再次将其反向连接回消息表，但仅限于匹配的日期/时间，然后连接到与您的服务商 ID 匹配的线程。以防万一在同一日期/时间下发布多条消息的可能性很小。不同的服务器

SELECT 
      T2.*, 
      M2.*
   FROM
      ( select T.thread_Customer_id,
               MAX( M.Message_Time ) as LatestPost
           from
              _Threads T
                 JOIN _Messages M
                    ON T.Thread_ID = M.Message_Thread_ID
           where
              T.thread_servicer_id = 'jG5s2b6TRs'
           GROUP BY 
              T.thread_customer_id ) PreMax

      JOIN _Messages M2
         on PreMax.LatestPost = M2.Message_Time

         JOIN _Threads T2
            on M2.Message_Thread_ID = T2.Thread_ID
           AND T2.Thread_Customer_ID = PreMax.Thread_Customer_ID
           AND T2.Thread_Servicer_ID = 'jG5s2b6TRs'

   ORDER BY 
      M2.Message_Time DESC