Как (быстро) выбрать книги, у которых есть все нужные авторы?

Для выборки книг, которые имеют все нужные авторы, мы можем использовать операторы подзапросов и агрегатные функции в MySQL. Процесс состоит из следующих шагов:

1. Создание таблицы книг с необходимыми столбцами, такими как идентификатор книги и название.

  CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100)
  );

2. Создание таблицы авторов с необходимыми столбцами, такими как идентификатор автора и имя.

  CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
  );

3. Создание таблицы, которая будет сопоставлять книги с авторами, используя внешние ключи.

  CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
  );

4. Заполняем таблицу книг и авторов тестовыми данными.

  INSERT INTO books (book_id, title) VALUES (1, 'Book 1');
  INSERT INTO books (book_id, title) VALUES (2, 'Book 2');
  INSERT INTO books (book_id, title) VALUES (3, 'Book 3');

  INSERT INTO authors (author_id, name) VALUES (1, 'Author 1');
  INSERT INTO authors (author_id, name) VALUES (2, 'Author 2');
  INSERT INTO authors (author_id, name) VALUES (3, 'Author 3');
  INSERT INTO authors (author_id, name) VALUES (4, 'Author 4');
  INSERT INTO authors (author_id, name) VALUES (5, 'Author 5');

  INSERT INTO book_authors (book_id, author_id) VALUES (1, 1);
  INSERT INTO book_authors (book_id, author_id) VALUES (1, 2);
  INSERT INTO book_authors (book_id, author_id) VALUES (2, 2);
  INSERT INTO book_authors (book_id, author_id) VALUES (2, 3);
  INSERT INTO book_authors (book_id, author_id) VALUES (3, 1);
  INSERT INTO book_authors (book_id, author_id) VALUES (3, 3);
  INSERT INTO book_authors (book_id, author_id) VALUES (3, 4);

5. Используем операторы подзапросов и агрегатные функции для выборки книг с нужными авторами.

  SELECT title
  FROM books
  WHERE book_id IN (SELECT book_id FROM book_authors WHERE author_id IN (1, 2, 3))
  GROUP BY book_id
  HAVING COUNT(DISTINCT author_id) = 3;

В данном примере, выборка будет содержать только книги, которые имеют всех нужных авторов (в данном случае, авторы с идентификаторами 1, 2 и 3). Агрегатная функция COUNT(DISTINCT author_id) подсчитывает количество уникальных идентификаторов авторов для каждой книги в результате запроса, и поэтому HAVING COUNT(DISTINCT author_id) = 3 гарантирует, что только те книги будут выбраны, которые имеют все нужные авторы.