MYSQL就像一个联接,但只需要最新的行? [英] MYSQL like a join but only need the newest row?
本文介绍了MYSQL就像一个联接,但只需要最新的行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在一个查询中执行以下操作:
I want to do the following but in one query:
$query = mysql_query("SELECT name FROM tbl_users WHERE category = '1'");
while($row = mysql_fetch_assoc($query))
{
$query2 = mysql_query("SELECT datecreated
FROM tbl_comments
ORDER BY datecreated DESC LIMIT 1");
$row2 = mysql_fetch_assoc($query2);
echo $row['name'] . " > " . $row2['datecreated'] ."<br />";
}
有一个用户表和一个注释表,我想显示一个用户列表以及他们旁边的最后一条注释的日期吗?
There is a user table and a comments table, I want to display a list of users and the date of the last comment next to them?
在单个查询中有可能吗?
Is this possible in a single query?
推荐答案
您可以使用以下SQL:
You can do so using the following SQL:
SELECT name,
(
SELECT datecreated
FROM tbl_comments
WHERE tbl_users.user_id = tbl_comments.user_id
ORDER BY datecreated LIMIT 1
)
FROM tbl_users
WHERE category = '1';
或使用:
SELECT tbl_users.name, MAX(datecreated) AS latestcomment
FROM tbl_users LEFT JOIN tbl_comments ON (tbl_users.user_id = tbl_comments.user_id)
GROUP BY tbl_users.name;
这篇关于MYSQL就像一个联接,但只需要最新的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文