我正在尝试进行一个简单的查询,从两个表中检索数据。一个表,论坛帖子保存与论坛中帖子有关的数据,另一个表,用户,保存有关我系统上用户的数据。
我遇到的问题是,当我想收集有关哪些用户发布了哪些帖子的数据时。
这是我的表格:
mysql> select * from ForumPosts;
+----+--------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| ID | UserID | Title | Post | ImgFile | SubmissionTime |
+----+--------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
| 1 | 1 | More tests | tests... | NULL | 2019-02-26 01:39:28 |
| 2 | 2 | Another test post | tests... | NULL | 2019-02-26 22:52:26 |
| 3 | 1 | Test post | tests… | NULL | 2019-03-27 22:10:38 |
+----+--------+-----------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------------+
mysql> select * from Users;
+----+----------------+----------+----------+---------------------+
| ID | Email | UserName | Password | DateRegistered |
+----+----------------+----------+----------+---------------------+
| 1 | test@test.com | tester | dank | 2019-02-26 22:43:26 |
| 2 | test2@test.com | 2tester | dank | 2019-02-26 22:43:26 |
+----+----------------+----------+----------+---------------------+
我尝试查看其他一些帖子,例如: 简单 MySQL 联接以替换外键 他们似乎都和我有同样的问题,但我似乎找不到我的解决方案有什么问题。
这是我当前使用的查询。
select
Title,
Users.ID as Author,
SubmissionTime as Posted
from ForumPosts
left join
Users on ForumPosts.UserID = Users.ID
order by SubmissionTime desc;
但返回的是:
mysql> select
-> Title,
-> UserId as Author,
-> SubmissionTime as Posted
-> from ForumPosts
-> left join
-> Users on ForumPosts.UserID = Users.ID
-> order by SubmissionTime desc;
+-----------------------------------------------------------------------------+--------+---------------------+
| Title | Author | Posted |
+-----------------------------------------------------------------------------+--------+---------------------+
| Test post | 1 | 2019-03-27 22:10:38 |
| Another test post | 2 | 2019-02-26 22:52:26 |
| More tests | 1 | 2019-02-26 01:39:28 |
+-----------------------------------------------------------------------------+--------+---------------------+
它的结果列出了用户的外键,但我想要他们的关联用户名。
我觉得这应该是一个非常简单的修复,但我似乎找不到它。
在 select 语句中添加Users.UserName,
,如下所示
select
Title,
Users.UserName,
Users.ID as Author,
SubmissionTime as Posted
from ForumPosts
left join
Users on ForumPosts.UserID = Users.ID
order by SubmissionTime desc;