MySQL:加入很多表

Question

我的 MySQL 数据库中有这些表用于一个简单的大学图书馆应用程序(出于显而易见的原因，我对它们进行了一些简化):

SELECT * FROM language;
╔═════════════╦══════════╗
║ language_id ║ language ║
╠═════════════╬══════════╣
║          11 ║  English ║
║          12 ║  Russian ║
║          13 ║   German ║
╚═════════════╩══════════╝

SELECT * FROM subject;
╔════════════╦═════════════╗
║ subject_id ║   subject   ║
╠════════════╬═════════════╣
║         21 ║ Mathematics ║
║         22 ║     History ║
║         23 ║   Chemistry ║
║         24 ║     Physics ║
╚════════════╩═════════════╝

SELECT publisher_id, publisher FROM publisher;
╔══════════════╦═══════════════╗
║ publisher_id ║   publisher   ║
╠══════════════╬═══════════════╣
║           31 ║ Berkley Books ║
║           32 ║       Penguin ║
╚══════════════╩═══════════════╝

SELECT author_id, first_name, last_name FROM author;
╔═══════════╦════════════╦═══════════╗
║ author_id ║ first_name ║ last_name ║
╠═══════════╬════════════╬═══════════╣
║        41 ║        Joe ║    Schmoe ║
║        42 ║       John ║     Smith ║
╚═══════════╩════════════╩═══════════╝

SELECT book_id, title, language_id, subject_id, publisher_id FROM book;
╔═════════╦═══════╦═════════════╦════════════╦══════════════╗
║ book_id ║ title ║ language_id ║ subject_id ║ publisher_id ║
╠═════════╬═══════╬═════════════╬════════════╬══════════════╣
║       1 ║     A ║          11 ║         22 ║           31 ║
║       2 ║     B ║          13 ║         24 ║           32 ║
╚═════════╩═══════╩═════════════╩════════════╩══════════════╝

SELECT book_id, author_id FROM book_author;
╔═════════╦════════════╗
║ book_id ║ author_id  ║
╠═════════╬════════════╣
║       1 ║         41 ║
║       1 ║         42 ║
║       2 ║         41 ║
╚═════════╩════════════╝

SELECT book_copy_id, book_id FROM book_copy;
╔══════════════╦═════════╗
║ book_copy_id ║ book_id ║
╠══════════════╬═════════╣
║            1 ║       1 ║
║            2 ║       1 ║
║            3 ║       1 ║
║            4 ║       2 ║
║            5 ║       2 ║
╚══════════════╩═════════╝

我有一个看起来像这样的查询(为了以防万一，我向您展示了它):

SELECT b.book_id,
       b.title,
       GROUP_CONCAT(CONCAT_WS(' ', a.last_name,
                                   a.first_name)
                              ORDER BY a.last_name
                              SEPARATOR ', ') AS authors,
       l.language,
       s.subject,
       p.publisher
FROM   book AS b
       LEFT JOIN book_author AS ba
              ON b.book_id = ba.book_id
       LEFT JOIN author AS a
              ON ba.author_id = a.author_id
       LEFT JOIN language AS l
              ON b.language_id = l.language_id
       LEFT JOIN subject AS s
              ON b.subject_id = s.subject_id
       LEFT JOIN publisher AS p
              ON b.publisher_id = p.publisher_id
GROUP BY b.title ASC;

╔═════════╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
║ book_id ║ title ║         authors        ║ language ║ subject ║   publisher   ║
╠═════════╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
║       1 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
║       2 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
╚═════════╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝

现在，我想要这个选择:

╔══════════════╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
║ book_copy_id ║ title ║         authors        ║ language ║ subject ║   publisher   ║
╠══════════════╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
║            1 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
║            2 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
║            3 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
║            4 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
║            5 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
╚══════════════╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝

我还有这两张表:

SELECT student_id, first_name, last_name FROM student;
╔════════════╦════════════╦═══════════╗
║ student_id ║ first_name ║ last_name ║
╠════════════╬════════════╬═══════════╣
║         81 ║        Bob ║     Dylan ║
║         82 ║        Jim ║    Carrey ║
╚════════════╩════════════╩═══════════╝

SELECT student_id, book_copy_id FROM loan;
╔════════════╦══════════════╗
║ student_id ║ book_copy_id ║
╠════════════╬══════════════╣
║         81 ║            1 ║
║         81 ║            4 ║
║         82 ║            5 ║
╚════════════╩══════════════╝

我需要编写一个生成此选择的查询:

-- 'a' stands for 'the quantity of book copies that are available for a loan'
-- 't' stands for 'the total quantity of book copies'
╔═══╦═══╦═══════╦════════════════════════╦══════════╦═════════╦═══════════════╗
║ a ║ t ║ title ║         authors        ║ language ║ subject ║ publisher     ║
╠═══╬═══╬═══════╬════════════════════════╬══════════╬═════════╬═══════════════╣
║ 2 ║ 3 ║     A ║ Joe Schmoe, John Smith ║  English ║ History ║ Berkley Books ║
║ 0 ║ 2 ║     B ║             John Smith ║   German ║ Physics ║       Penguin ║
╚═══╩═══╩═══════╩════════════════════════╩══════════╩═════════╩═══════════════╝

我希望我已经用图表非常明确地概述了它。请打扰我写下这两个问题的查询。

Answer 1

您需要加入book_copy 并更改group by 条件。这解决了您的第一个问题:

SELECT bc.book_copy_id,
       b.title,
       GROUP_CONCAT(CONCAT_WS(' ', a.last_name,
                                   a.first_name)
                              ORDER BY a.last_name
                              SEPARATOR ', ') AS authors,
       l.language,
       s.subject,
       p.publisher
FROM   book AS b
       LEFT JOIN book_author AS ba
              ON b.book_id = ba.book_id
       LEFT JOIN author AS a
              ON ba.author_id = a.author_id
       LEFT JOIN language AS l
              ON b.language_id = l.language_id
       LEFT JOIN subject AS s
              ON b.subject_id = s.subject_id
       LEFT JOIN publisher AS p
              ON b.publisher_id = p.publisher_id
       left join book_copy bc
              on b.book_id = bc.book_id
GROUP BY bc.book_copy_id ASC;