php - Mysql Query Join Table 最近一行

我有一个类别页面，在我加入之前它们运行良好。
类别显示如下:

现在它说的是讨论，我需要根据它的 discussion_id 显示发布到该类别的最后一个讨论。我试过 ORDER BY ... DESC 但它按类别名称排序，而不是按讨论名称和发布者排序。

 $sql = "SELECT *, COUNT(d.cat_id) as count
         FROM discussions as d
         LEFT JOIN categories c ON (c.cat_id = d.cat_id)
         RIGHT JOIN soldiers s ON (s.uid = d.discussion_poster)
         GROUP BY d.cat_id";
 $result = query($sql);


while (($row = mysqli_fetch_assoc($result)) != false) {
   $cat_id = $row['cat_id'];
   $discussion_id = $row['discussion_id'];
   $cat_title = $row['cat_title'];
   $discussion_title = $row['discussion_title'];
   $discussion_time = $row['discussion_time'];
   $count = $row['count'];
   $discussion_poster_id = $row['discussion_poster'];
   $discussion_poster = $row['soldier'];
}

最佳答案

$sql = "
SELECT *
FROM categories c
INNER JOIN
(
   SELECT MAX(discussion_id) discussion_id,
   COUNT(discussion_id) as count,
   cat_id
   FROM
   discussions
   GROUP BY
   cat_id
) as d1
ON (c.cat_id = d1.cat_id)
INNER JOIN discussions as d
ON (d1.discussion_id = d.discussion_id)
INNER JOIN soldiers s
ON (s.uid = d.discussion_poster)
GROUP BY d.cat_id";

关于php - Mysql Query Join Table 最近一行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36783095/