mysql - 从数据透视表返回单行

Question

我有以下三个表

Create table posts (
    post_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    post_title Varchar(255) NOT NULL,
 Primary Key (post_id)) ENGINE = InnoDB;

Create table publishers (
    publisher_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    publisher_name Varchar(255) NOT NULL,
 Primary Key (publisher_id)) ENGINE = InnoDB;

Create table publisher_terms (
    term_id Int UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id Int UNSIGNED NOT NULL,
    publisher_id Int UNSIGNED NOT NULL,
 Primary Key (term_id)) ENGINE = InnoDB;

我想检索只有一个发布者的所有帖子。我尝试过以下内部查询，但我收到未知列错误。

select P.post_title FROM posts P, (SELECT PB.publisher_id FROM publisher_terms PB WHERE P.post_id=PB.post_id LIMIT 1) PT;

Answer 1

您可以使用此帖子向发布者检索该帖子。由于帖子标题可能不唯一，因此我在结果中包含 post_id。

select P.post_id, P.post_title, MAX(PS.publisher_name) publisher_name
FROM publisher_terms T
   JOIN posts P
      ON T.post_id = P.post_id
   JOIN publishers PS
      ON T.publisher_id = PS.publisher_id
GROUP BY P.post_id, P.post_title