mysql - 如何使两个结果集相交？

Question

我有两个表library和book，如这两个 block 所示

bookid   title  author  publisher 
0        ABC    Abc     abc 
1        DEF    Def     def
2        GHI    Ghi     abc
3        ...    ...     ...

id       discount   price
0         50          8
1         75          6
2         33         10
3         ..         ..

我需要选择每个作者的作品、价格和折扣，其中出版商至少有 2 个标题且不超过 9 个。第一个查询可能是

SELECT title, author, publisher, price, discount FROM library, book

这显然返回了所有数据。第二个查询可能是

SELECT publisher FROM library GROUP BY publisher 
HAVING COUNT(bookid) > 2 AND COUNT(bookid) < 9

这没问题，但它不会返回作者、标题和价格。我想将两个结果集相交。我尝试过以下查询

SELECT title, author, publisher, price, discount FROM library LEFT JOIN
book ON library.bookid = book.id WHERE publisher IN ( SELECT publisher
FROM library GROUP BY publisher HAVING COUNT(bookid) < 9 AND 
COUNT(bookid) > 2 ) ORDER BY publisher ASC

输出应该是:

Title  Author  Publisher Price   Discount
ABC    Abc     abc       8       ...
GHI    Ghi     abc       10      ...
...    ...     def       11      ...
...    ...     def       11      ...
...    ...     def       9       ...

它工作得很好，但是在 100 条记录上它非常慢，并且当有超过 1000 条记录时它根本不起作用。如何优化我的查询？提前致谢

Answer 1

这基本上是您的查询:

SELECT l.title, l.author, l.publisher, b.price, b.discount
FROM library l LEFT JOIN
     book b
     ON l.bookid = b.id
WHERE l.publisher IN (SELECT l.publisher
                      FROM library l
                      GROUP BY l.publisher
                      HAVING COUNT(*) < 9 AND COUNT(*) > 3
                     )
ORDER BY l.publisher ASC;

首先，我将 IN 逻辑移至 FROM 子句:

SELECT l.title, l.author, l.publisher, b.price, b.discount
FROM library l LEFT JOIN
     book b
     ON l.bookid = b.id JOIN
     (SELECT l.publisher
      FROM library l
      GROUP BY l.publisher
      HAVING COUNT(*) < 9 AND COUNT(*) > 3
     ) ll
     ON l.publisher = ll.publisher
ORDER BY l.publisher ASC;

然后，我认为这些索引可能会对book(id)、library(publisher, bookid)有所帮助。