php - 如何获取mysql中组中的最新行？

Question

在我的 mysql 查询中，我尝试获取所有线程及其最新行。

    $query = "SELECT th.id, tm.message, tm.date_sent, tm.date_sent>tu.last_read_date AS new
              FROM thread th
              JOIN thread_user tu ON th.id=tu.thread_id AND tu.user_id={$user_id}
              JOIN thread_message tm ON th.id=tm.thread_id
              JOIN (
                   SELECT thread_id, MAX(date_sent) date_sent
                   FROM thread_message
                   GROUP BY thread_id
              ) q ON tm.thread_id = q.thread_id AND tm.date_sent = q.date_sent
              ORDER BY tm.date_sent DESC";  

这可行，但问题是，如果有两行或更多行的日期是最新的并且它们是相同的日期，那么它将与它们连接。我需要第三个连接语句来连接至多 1 行。

我也不想假设最大的 id 意味着它是最新的行，因为我以后总是可以手动更改日期。

有人知道如何解决这个问题吗？

谢谢

Answer 1

实现此目的的一种方法是为每个组建立一个行号，在本例中，您的组是 thread_id 和 date_sent。使用MySql，您需要使用用户定义变量来执行此操作:

SELECT th.id, 
    tm.message, 
    tm.date_sent, 
    tm.date_sent>tu.last_read_date AS new
FROM thread th
    JOIN thread_user tu ON th.id=tu.thread_id AND tu.user_id={$user_id}
    JOIN (
        SELECT id,
               thread_id, 
               message, 
               date_sent, 
               @rn:=IF(@prevthread_id=thread_id, @rn+1, 1) rn,
               @prevthread_id:=thread_id
        FROM thread_message, (SELECT @rn:=1, @prevthread_id:=0) t
        ORDER BY thread_id, date_sent DESC, id
    ) tm ON th.id=tm.thread_id
           AND tm.rn = 1 
ORDER BY tm.date_sent DESC

<小时/>

也许这对你来说更容易(但只是因为你使用mysql):

SELECT th.id, 
    tm.message, 
    tm.date_sent, 
    tm.date_sent>tu.last_read_date AS new
FROM thread th
    JOIN thread_user tu ON th.id=tu.thread_id AND tu.user_id={$user_id}
    JOIN thread_message tm ON th.id=tm.thread_id
    JOIN (
        SELECT thread_id, 
               id, 
               MAX(date_sent) date_sent
        FROM thread_message
        GROUP BY thread_id
    ) q ON tm.thread_id = q.thread_id 
           AND q.id = tm.id 
           AND tm.date_sent = q.date_sent
ORDER BY tm.date_sent DESC

这将返回要加入的任意 id。