mysql找到最近的用户评论 [英] mysql find recent user comments

问题描述

我有2个表:用户和注释.

I have 2 tables: user and comment.

create table user(
  userID int auto_increment,
  userName varchar(10),
  userCreatedDate timestamp,
  primary key(userID)
);

create table comment(
  commentID int auto-increment,
  userID int,
  comment varchar(100),
  primary key(commentID),
  foreign key(userID) references user(userID)
);

我希望我的输出如下所示:

And I want my output like the following:

我想从数据库中找到最近的评论: 但是，我这样尝试，但无法获得输出:

I want to find recent comment from the database: However, I tried like this and cannot get my output:

select u.userID, c.commentID, u.userCreateDate
from comment c
left join userID u on c.userID = s.userID
order by u.userCreateDate desc

我应该如何修改才能获得输出?

How should I modify so that I can get my output?

推荐答案

您不想通过user表中的userCreateDate对其进行排序，因为对于所有注释而言都是相同的.拥有commentCreateDate对您来说会很好，但是由于您没有commentCreateDate，因此可以通过commentID进行订购.

You don't want to order it by the userCreateDate from the user table, because that will be the same for all comments. It would have been nice for you to have a commentCreateDate but since you don't have that you can order it by the commentID.

更改

order by u.userCreateDate desc

到

order by c.commentID desc

编辑后添加:

您还需要更改选择以获取实际评论.

You also need to change your select to get the actual comment.

select u.userID, c.commentID, u.userCreateDate

应该是

select u.userID, c.commentID, c.comment, u.userCreateDate

以获得所需的输出.另外，我认为您不希望在输出中包含userCreateDate，因为人们可能会将其与撰写评论的日期混淆.

to get your desired output. Plus I don't think you want to include userCreateDate in the output as people could confuse that with the date the comment was written.

这篇关于mysql找到最近的用户评论的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！