mysql - 查询以在三个表上使用内连接选择随机值

Question

我有一个带有树表的数据库，

person: id, bio, name
book: id, id_person, title, info
file: id, id_book, location

其他信息:书籍约 50,000 行，文件约 300,000 行。

我想做的是选择 12位不同的作者，并选择仅一本书并从该书中 从表文件中选择位置。

我尝试过以下内容:

SELECT DISTINCT(`person`.`id`), `person`.`name`, `book`.`id`, `book`.`title`, `book`.`info`, `file`.`location`
FROM `person`
INNER JOIN `book`
ON `book`.`id_person` = `person`.`id`
INNER JOIN `file`
ON `file`.`id_book` = `book`.`id`
LIMIT 12

我了解到 DISTINCT 并不像人们预期的那样工作。还是我错过了什么？上面的代码返回同一作者的书籍并与下一本书一起使用。这不是我想要的。我想要 12 位不同作者的每一本书。

从数据库检索此信息的正确方法是什么？另外，我想检索 12 个随机人。不是在数据库中按连续顺序存储的人。我无法使用 rand() 制定任何查询，因为我什至无法获得不同的作者。

我使用 MariaDB。我将不胜感激任何帮助，尤其是能让我以出色的性能做到这一点的帮助。

Answer 1

在 MySQL 中，您实际上可以使用 GROUP BY 来做到这一点

SELECT p.`id`, p.`name`, b.`id`, b.`title`, b.`info`, f.`location`
FROM `person` p INNER JOIN
     `book` b
     ON b.`id_person` = p.`id` INNER JOIN
     `file` f
     ON f.id_book = b.id
GROUP BY p.id
ORDER BY rand()
LIMIT 12;

但是，这并不能保证从同一行返回非 id 值(尽管实际上确实如此)。而且，虽然作者是随机的，但书籍和地点却不是。

一致地执行此操作的 SQL 查询有点复杂:

SELECT p.`id`, p.`name`, b.`id`, b.`title`, b.`info`,
      (SELECT f.location
       FROM file f
       WHERE f.id_book = b.id
       ORDER BY rand()
       LIMIT 1
      ) as location
FROM (SELECT p.*,
             (SELECT b.id
              FROM book b
              WHERE b.id_person = p.id 
              ORDER BY rand()
              LIMIT 1
             ) as book_id
      FROM person p
      ORDER BY rand()
      LIMIT 12
     ) p INNER JOIN
     book b
     ON b.id = p.book_id ;