如何在MySQL中将两个相同的表连接在一起

Question

下面是我的数据库模式中的三个表：

-- Table where I store authors
SELECT author_id, first_name, last_name FROM author;
╔═══════════╦════════════╦═══════════╗
║ author_id ║ first_name ║ last_name ║
╠═══════════╬════════════╬═══════════╣
║         1 ║     Ernest ║ Hemingway ║
║         2 ║       Walt ║   Whitman ║
║         3 ║       Mark ║     Twain ║
║       ... ║        ... ║       ... ║
╚═══════════╩════════════╩═══════════╝

-- Junction-table to keep track of books and their respective authors
SELECT book_id, author_id FROM book_author;
╔═════════╦═══════════╗
║ book_id ║ author_id ║
╠═════════╬═══════════╣
║      37 ║         1 ║
║      37 ║         2 ║
║     ... ║       ... ║
╚═════════╩═══════════╝

-- Temporary table to store, once again, books and their respective authors
-- but only for updating book purposes. The table is identical in its structure
-- to the book_author table
SELECT book_id, author_id FROM temp_book_author;
╔═════════╦═══════════╗
║ book_id ║ author_id ║
╠═════════╬═══════════╣
║      37 ║         3 ║
║     ... ║       ... ║
╚═════════╩═══════════╝

现在，我可以使用下面的查询来获得以下结果：

SET    @BOOK_ID = 37;
SELECT @BOOK_ID AS book_id,
       a.last_name,
       a.first_name
FROM   book_author AS ba
       LEFT JOIN author AS a
              ON ba.author_id = a.author_id
WHERE  book_id = @BOOK_ID;
╔═════════╦════════════╦═══════════╗
║ book_id ║ first_name ║ last_name ║
╠═════════╬════════════╬═══════════╣
║      37 ║     Ernest ║ Hemingway ║
║      37 ║       Walt ║   Whitman ║
╚═════════╩════════════╩═══════════╝

下面是我想要实现的目标:我需要添加该行(如果有更多行的话)，它与从temp_book_author表到上面选择的ID为37的书相关联，或者，如果您愿意的话，可以添加两个表，book_author和temp_book_author，即一个表，就好像它们是一个表一样：

╔═════════╦════════════╦═══════════╗
║ book_id ║ first_name ║ last_name ║
╠═════════╬════════════╬═══════════╣
║      37 ║     Ernest ║ Hemingway ║
║      37 ║       Walt ║   Whitman ║
║      37 ║       Mark ║     Twain ║
╚═════════╩════════════╩═══════════╝

我该如何处理这个问题？

Answer 1

使用联盟：

SET    @BOOK_ID = 37;
SELECT @BOOK_ID AS book_id,
       a.last_name,
       a.first_name
FROM   (SELECT * FROM book_author
        UNION
        SELECT * FROM temp_book_author) AS ba
       LEFT JOIN author AS a
              ON ba.author_id = a.author_id
WHERE  book_id = @BOOK_ID;

您还可以创建一个自动合并两个表的表：

CREATE TABLE union_book_author (book_id int, author_id int)
ENGINE = MERGE
UNION = (book_author, temp_book_author);

然后，可以在查询中使用union_book_author。

Answer 2

我同意史考克的意见，即理想情况下，您应该将来自temp_book_author的数据插入到book_author中，然后从这个表中进行选择，但是如果有不想这样做的原因，则需要使用'union‘命令。

SET    @BOOK_ID = 37;
SELECT @BOOK_ID AS book_id, a.last_name, a.first_name
FROM   book_author AS ba 
LEFT JOIN author AS a ON ba.author_id = a.author_id
WHERE  book_id = @BOOK_ID
UNION
select t.book_id, a1.last_name, a1.firstname
from temp_book_author as t
left join author as a1 on t.author_id = a1.author_id
where t.book_id = @BOOK_ID