mysql - 如何在一个查询中从 3 个不同的表中获取 3 个参数 - SQL

Question

我有这些表:

CREATE TABLE User( 
Username varchar(15) NOT NULL, 
Name varchar(20) DEFAULT '', 
Surname varchar(20) DEFAULT '', 
Email varchar(30) DEFAULT '', 
City varchar(20) DEFAULT '', 
Description varchar(1000) DEFAULT '', 
userID INTEGER NOT NULL, 
Primary key(Username, userID), 
Foreign key(userID) references Followers(userID) 
);"

CREATE TABLE Followers( 
userID INTEGER NOT NULL, 
usernameFollower varchar(15), 
Primary key(userID), 
Foreign key(usernameFollower) references User(Username) 
);"

CREATE TABLE Post( 
Username varchar(15) NOT NULL, 
userID INTEGER NOT NULL, 
Date DATETIME NOT NULL, 
Message VARCHAR(500) NOT NULL, 
Primary key(Username, Date), 
Foreign key(Username) references User(Username) 
);"

我需要通过查询获得一个列表，其中包含用户中的所有用户，以及每个用户发布最新帖子的日期和他们拥有的关注者数量。

例如，输出必须是这样的:

USERNAME        LATEST POST                 FOLLOWERS
user1           2011/11/11 18:30:11         5
user2           2011/10/05 17:30:00         1
user3           2010/05/11 18:30:11         90
user4           2011/11/11 18:30:11         5

我不知道该怎么做。我假设我需要使用 COUNT() 来计算关注者的数量，但我如何才能将它链接到用户的最新帖子？只需要一个查询就可以吗？还是我需要使用 View ？

提前致谢，致以最诚挚的问候。

Answer 1

您需要通过用户 ID 连接所有 3 个表。然后，您可以通过按用户 ID 分组来选择最新的发布日期。最初，我也加入了 Followers 表，但这有一个意想不到的副作用，所以......

类似于:

SELECT U.Name AS UserName,
       MAX(P.Date) AS LatestPost,
       (SELECT COUNT(F.userNameFollower) FROM Followers WHERE userID = U.userID) AS FollowerCount
FROM User U
INNER JOIN Post P ON (U.userID = P.userID)

GROUP BY U.UserID

应该可以。

请注意，我的查询假设用户已发布。如果还没有，则将 INNER JOIN 更改为 LEFT JOIN。

原始查询不起作用的原因是它为每个用户匹配 Post 中的一行，并为每个用户匹配 Followers 中的一行 - 从而成倍增加结果数。相反，我们可以使用子查询来选择用户表中具有匹配用户 ID 的关注者数量。

但是...这只计算用户拥有的关注者总数，而不是帖子的关注者数量。这是因为您只将关注者链接到用户。您还需要在 Follower 和 Post 之间建立关系。

您可以通过多种方式做到这一点；您可以创建一个由 PostId 和 UserId 组成的 PostFollower 表(这样您就可以看到哪个用户关注了每个帖子)或者您可以将 PostId 添加到 Follower 表中，并存储关注者的 userId 和 PostId，这样您就可以存储哪个用户关注了哪些帖子。