MySQL:有效地为IN子句中的每个项目获取一行 [英] MySQL: Get one row for each item in the IN clause efficiently

问题描述

我有一个带有很多表的MySQL数据库，并且我有两个表，它们使用第三个表以多对多的关系相互链接.这是它的外观的简化演示:

I have a MySQL database with a lot of tables, and I have two tables that are linked to each other in a many to many relationship, using a third table. Here's a simplified demonstration of how it looks like:

表1:书籍

• 第1行:ID:15标题:虚拟测试本1
• 第二排:ID:18标题:Dummy Testbook 4
• 第3行:ID:22标题:Dummy Testbook 6
• 第4行:ID:10标题:Dummy Testbook 8
• 第5行:ID:16标题:虚拟测试本15
• ...

• Row 1: ID: 15 Title: Dummy Testbook 1
• Row 2: ID: 18 Title: Dummy Testbook 4
• Row 3: ID: 22 Title: Dummy Testbook 6
• Row 4: ID: 10 Title: Dummy Testbook 8
• Row 5: ID: 16 Title: Dummy Testbook 15
• ...

表2:作者

• 行1:ID:4名称:Dave
• 第2行:ID:8名称:Robert
• 第3行:ID:12名称:Amy

• Row 1: ID: 4 Name: Dave
• Row 2: ID: 8 Name: Robert
• Row 3: ID: 12 Name: Amy

表3:books_authors

第1行:author_id:4 book_id:15

第1行:author_id:8 book_id:22

第1行:author_id:8 book_id:10

第1行:author_id:12 book_id:16

Table 3: books_authors

Row 1: author_id: 4 book_id: 15

Row 1: author_id: 8 book_id: 22

Row 1: author_id: 8 book_id: 10

Row 1: author_id: 12 book_id: 16

我想做的是获取一个随机的作者列表，其中包括每位作者的最新著作，因此我要获得一个包含所有作者ID的列表，并使用一些脚本创建该随机列表，然后使用以下查询:

What I am trying to do is to get a random list of authors with the latest book per each author, so I am getting a list with all author ids, using some scripting to create that random list, and then I am using the following query:

SELECT 
`books`.`ID`, 
`books`.`Name` 
FROM `books` 
  LEFT JOIN `books_authors` ON `books`.`ID` = `books_authors`.`book_id`
  LEFT JOIN `authors` ON `books_authors`.`author_id` = `authors`.`ID` 
WHERE  `authors`.`ID` IN(8, 12) 
LIMIT 1

问题是，限制1意味着我只会得到一本书，而我想得到两本书，每位作者一本书.如何在不为每个作者运行查询的情况下做到这一点? (数据库很大，对每个作者的查询都会使服务器爬网).如果我增加该限制，那么我不一定会彼此得到两本书，但是我可能会得到同一作者的两本书.

The problem is, the Limit 1 means that I will only get one book, while I want to get two books, one per each author. How can I do that without running a query for each author? (the database is huge, and a query for each author will bring the server to a crawl). If I increase the limit, then I am not necessarily getting two books one per each other, but I may get two books by the same author.

换句话说，我希望限制是在IN而不是整个查询上.那可能吗?如果没有，是否有一种有效的方式来做我想做的事?

In other words, I want the limit to be on the IN, not on the entire query. Is that possible? and if not, is there an efficient way of doing what I am trying to do?

谢谢！

大卫

推荐答案

您可以先为每位具有最新ID的作者书籍选择，然后将其与books表结合起来以获取名称.像这样的东西:

You can first select the for each author books with the latest id and join it with books table to get names. Something like that:

SELECT 
`books`.`ID`, 
`books`.`Name` 
FROM `books`
  INNER JOIN (
    select max(book_id), author_id
    from `books_authors`
    group by author_id) ba ON `books`.`ID` = ba.`book_id`
WHERE ba.author_id IN (8, 12)

这篇关于MySQL:有效地为IN子句中的每个项目获取一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！