Reddit克隆项目：多表查询如何获取发帖用户用户名？

你只需要给第二次关联的profiles表起一个别名，就能区分用于过滤用户的profiles表和用于获取发帖用户信息的profiles表。

修改后的SQL语句如下：

SELECT DISTINCT 
    posts.title, 
    posts.description, 
    posts.profile_id, 
    subreddits.id,
    poster.username AS post_author -- 新增发帖用户名字段
FROM 
    followed_subreddits
JOIN 
    subreddits ON followed_subreddits.subreddit_id = subreddits.id
JOIN 
    profiles ON followed_subreddits.profile_id = profiles.id -- 关联当前用户的profiles，用于过滤
JOIN 
    posts ON followed_subreddits.subreddit_id = posts.subreddit_id
JOIN 
    profiles AS poster ON posts.profile_id = poster.id -- 别名poster，关联发帖用户的profiles
WHERE 
    profiles.username = "kkingsbe";

关键说明： #

• 第一次关联profiles是为了匹配当前目标用户（kkingsbe），过滤出该用户关注的子版块；
• 第二次关联profiles时用AS poster给表起别名，通过posts.profile_id = poster.id关联到发布该帖子的用户，从而获取其username；
• 使用AS post_author可以给返回的用户名字段起一个更清晰的别名，方便后端处理。

原查询及表结构参考 #

原查询语句：

SELECT DISTINCT 
    posts.title, posts.description, posts.profile_id, subreddits.id
FROM 
    followed_subreddits
JOIN 
    subreddits ON followed_subreddits.subreddit_id = subreddits.id
JOIN 
    profiles ON followed_subreddits.profile_id = profiles.id
JOIN 
    posts ON followed_subreddits.subreddit_id = posts.subreddit_id
WHERE 
    profiles.username = "kkingsbe";

各表结构：

CREATE TABLE `profiles` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `username` varchar(255) NOT NULL,
    `hashed_pw` binary(60),
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `subreddits` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `posts` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `profile_id` int NOT NULL,
    `subreddit_id` int NOT NULL,
    `title` varchar(255) NOT NULL,
    `description` varchar(8000),
    `link` varchar(1000),
    `upvotes` int DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `followed_subreddits` 
(
    `id` int NOT NULL AUTO_INCREMENT,
    `profile_id` int NOT NULL,
    `subreddit_id` int NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

内容的提问来源于stack exchange，提问作者Kyle Kingsberry