在最近的行上使用Mysql查询连接表

Question

在最近的行上使用Mysql查询连接表

4

我有一个类别页面，它们在进行联接之前运作正常。类别的显示如下：

category 1-----------------------0----------0 discussion 1 by someone category 2-----------------------0----------0 discussion 2 by someoneelse 现在，在讨论处，我需要根据其“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'];
}

- user6116249

我认为您应该提供更多关于表的信息，例如表结构、示例数据等等;) - Blank

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- trex005 · Answer 1

$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";