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
Table 3: books_authors
我想做的是获取一个随机的作者列表,其中包括每位作者的最新著作,因此我要获得一个包含所有作者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屋!