php – Mysql查询最近一行的连接表

发布时间：2020-12-13 22:26:14 所属栏目：PHP教程来源：网络整理

导读：我有一个类别的页面,他们工作正常,直到我加入. 类别显示如下： category 1———————–0———-0 discussion 1 by someone category 2———————–0———-0 discussion 2 by someoneelse 现在它在讨论的地方,我需要根据它的discussion_id显示发布到

我有一个类别的页面,他们工作正常,直到我加入.

类别显示如下：

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'];
}

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!