首页 文章

无法使用JOIN MYSQL确定WHERE子句应该是什么

提问于
浏览 次
6

我坚持创建一个MySQL查询 . 贝娄是我的数据库结构 .

authors （author_id和author_name）

books （book_id和book_title）

books_authors 是链接表（book_id和author_id）

所有书籍和作者的结果 - http://i.stack.imgur.com/VLzXn.png

我需要为某位作者获取所有书籍，但如果一本书有2位作者，则必须同时显示第二本书 . 例如，book_id = 2的书“Good Omens”有两位作者 . 当我运行查询时，我得到了author_id = 1的书籍，但我不能在结果中包含第二个作者 - “Neil Gaiman” . 查询是：

SELECT * FROM books 
   LEFT JOIN books_authors 
       ON books.book_id=books_authors.book_id 
   LEFT JOIN authors 
       ON books_authors.author_id=authors.author_id 
WHERE books_authors.author_id=1

以下是结果：

http://i.stack.imgur.com/PypqZ.png

我希望你能理解我，我们将不胜感激 .

mysql sql database join mysqli

3 回答

  • 2

    您不需要子查询：

    SELECT *
FROM book_authors ba
   JOIN books b
     ON b.book_id = ba.book_id
   JOIN book_authors ba2
     ON ba2.book_id = b.book_id
   JOIN authors a
     ON a.author_id = ba2.author_id
WHERE ba.author_id = 1
    回复于
  • 0

    您需要更改WHERE子句以执行如下的子选择：

    SELECT b.*, a.*
FROM books b
LEFT JOIN books_authors ba ON ba.book_id  = b.book_id
LEFT JOIN authors       a  ON a.author_id = ba.author_id
WHERE b.book_id IN (
  SELECT book_id
  FROM books_authors
  WHERE author_id=1)

    您的查询的问题是WHERE子句不仅过滤您在结果集中获得的书籍，还过滤书籍 - 作者关联 .

    使用此子查询，您首先使用作者ID过滤书籍，然后使用这些书ID来获取所有关联的作者 .

    顺便说一下，我确实认为在这种特定情况下用INNER JOIN替换OUTER JOIN的建议应该适用 . books_authors上的第一个LEFT OUTER JOIN肯定是无用的，因为WHERE子句保证该表中每个选定的book_id至少有一行 . 第二个LEFT OUTER JOIN可能没用，因为我希望author_id是authors表的主键，我希望books_authors表在author_id上有一个外键和一个NOT NULL约束...这意味着你不应该有books_authors行，不引用特定作者行 .

    如果这是真的并且已经确认，那么查询应该是：

    SELECT b.*, a.*
FROM books b
JOIN books_authors ba ON ba.book_id  = b.book_id
JOIN authors       a  ON a.author_id = ba.author_id
WHERE b.book_id IN (
  SELECT book_id
  FROM books_authors
  WHERE author_id=1)

    请注意，在大多数情况下，INNER JOIN可能比OUTER JOIN更有效（它们为引擎提供了更多关于如何执行stament和获取结果的选择） . 因此，如果不是绝对必要的话，你应该避免使用OUTER JOIN .

    我添加了别名并从结果集中删除了冗余列 .

    回复于
  • 5

    你非常接近......基本上你需要识别author_id =？的所有独特书籍ID . 然后再次将其与book_author表连接，以使所有作者与这些书ID相关联 . 然后加入书籍和作者，获取您的书籍和作者姓名 .

    希望以下内容在这方面非常清楚，但如果不是让我知道，我会帮助更详细地解释它

    SELECT a.*, d.* FROM books as a
   INNER JOIN (SELECT book_id FROM books_authors WHERE author_id=?) as b
       ON a.book_id=b.book_id 
   INNER JOIN books_authors  as c
       ON b.book_id=c.book_id
   INNER JOIN authors AS d
       ON d.author_id  = c.author_id

    顺便说一句，你也可以使用WHERE EXISTS子句来构造它 . 我不认为你会在任何方面看到很多性能差异，但仅仅是因为如果需要，你可以试试 . 使用EXPLAIN查看查询的执行计划 . 如果它有问题，还有其他方法可以给这只猫留下皮肤 .

    另外，请务必注意指数 . 无论您使用此处的方法，还是使用Frazz描述的方法，复合/多列/复合索引都可能对您产生重大影响 . 也就是说，考虑通过book_id和（author_id，book_id）索引books_authors . 是否应该使用额外的连接或IN或EXISTS子查询......很多方法可以使猫皮肤 . 无论如何，在books_authors上有一个多列索引可能会帮助你，特别是如果这个表很大的话

    回复于

相关问题