查询与回答表和帖子和用户2列同名的连接

Question

我从查询中检索到发帖者的用户名和回答者的用户名，但是2个表返回发帖者和回答者的用户名列，但是我想对发帖者的用户名列和回答者的用户名列进行区分，我如何更改在查询中回答的人的用户名列名。

发表文章的用户具有与发表文章的用户名相同的列username和回答相同的列answersusername。

如何更改回答为answerusername的用户名?

或者我应该设置2个查询来实现这一点?

查询

SELECT *
FROM [Post]
JOIN [User] ON [Post].userId = [User].id 
JOIN Answer ON [Post].id = [Answer].postId
JOIN [User] as UserAnswer on Answer.userId = [UserAnswer].id
WHERE [Post].id = 1

Answer 1

问题是您正在使用*来指定输出列—您只需要使用AS关键字来指定输出列名，就像这样:

SELECT post.*, answer.*,
user.username as post_username, 
useranswer.username as answer_username
FROM [Post]
JOIN [User]  ON [Post].userId = [User].id 
JOIN Answer ON [Post].id = [Answer].postId
JOIN [User] as UserAnswer on Answer.userId = [UserAnswer].id
where [Post].id = 1