mysql - 如何显示从两列过滤出的多个值

Question

我有作业，必须从两个表(书籍和作者)中选择数据。书籍有两位作者，我需要显示结果，书名和作者用逗号分隔。

我自己尝试过，但出现错误。

架构 SQL:

CREATE TABLE IF NOT EXISTS `author` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(128),
  `surname` VARCHAR(128),
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `book` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(128),
  `author_id` INT NOT NULL,
  `author_id_2` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB;

查询SQL:

INSERT INTO `author` (`name`, `surname`) VALUES ("William", "Shakespeare");
INSERT INTO `author` (`name`, `surname`) VALUES ("Agatha", "Christie");
INSERT INTO `author` (`name`, `surname`) VALUES ("J. K", "Rowling");
INSERT INTO `author` (`name`, `surname`) VALUES ("George", "Orwell");
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Hamlet", 1, 2);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Macbeth", 1, 3);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Murder on the Orient Express", 2, 3);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("The Secret of Chimneys", 2, 1);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("1984", 3, 1);
INSERT INTO `book` (`title`, `author_id`, `author_id_2`) VALUES ("Animal Farm", 3, 2);

查询:

SELECT 
    b.title Pavadinimas, 
    concat(concat(a1.name, ' ', a1.surname), ', ', concat(a2.name, ' ', a2.surname) Autoriai 
FROM book b 
INNER JOIN author a1 WHERE book.author_id=a1.id 
INNER JOIN author a2 WHERE book.author_id_2=a2.id;

结果应该是:

Hamlet | William Shakespeare, Agatha Christie

Answer 1

您需要将图书表与作者表连接两次，例如:

SELECT 
    b.title, 
    CONCAT(a1.name, ' ', a1.surname, ', ', a2.name, ' ', a2.surname)  authors
FROM book b 
INNER JOIN author a1 ON b.author_id = a1.id
INNER JOIN author a2 ON b.author_id_2 = a2.id

这个 demo on DB Fiddle 与您的示例数据返回:

| title                        | authors                              |
| ---------------------------- | ------------------------------------ |
| Hamlet                       | William Shakespeare, Agatha Christie |
| Macbeth                      | William Shakespeare, J. K Rowling    |
| Murder on the Orient Express | Agatha Christie, J. K Rowling        |
| The Secret of Chimneys       | Agatha Christie, William Shakespeare |
| 1984                         | J. K Rowling, William Shakespeare    |
| Animal Farm                  | J. K Rowling, Agatha Christie        |

注意:如果您的图书只有一位作者，则可以使用 LEFT JOIN 而不是 INNER JOIN。