php - 在单个查询中从多个表获取数据 -mysql

Question

我使用这些查询来选择读过类似书籍的用户的 user_id。

SELECT r2.user_id
FROM `read` r1
JOIN `read` r2
ON r1.user_id <> r2.user_id AND r1.book_id = r2.book_id
WHERE r1.user_id = 1
GROUP BY r2.user_id
HAVING count(*) >= 5

但我不想简单地显示 user_id。还有来自其他表的有关此 user_id 的数据!

上面的查询仅使用此表:

CREATE TABLE `read` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `book_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `No duplicates` (`user_id`,`book_id`),
  KEY `book_id` (`book_id`),
  CONSTRAINT `connections_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `connections_ibfk_2` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

但我也有:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `email` char(255) NOT NULL DEFAULT '',
  `password` char(12) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

和

CREATE TABLE `books` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `book` char(55) NOT NULL DEFAULT '',
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `book` (`book`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `books_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

因此，对于我的查询生成的列表中的每个 user_id，我还希望拥有来自 users 的电子邮件。以及该用户读取的所有 book_id，但这些 book_ids 是 id，我希望它显示基于 book_ids 的书籍中的书籍。

哇，有人能看懂我写的东西吗？

:L)

Answer 1

试试这个。希望没有语法错误。如果不起作用，请访问 SQLFiddle.com 并加载一些测试数据

SELECT users.id, users.email, book.books
FROM (
  SELECT r2.user_id
  FROM `read` r1
  JOIN `read` r2
    ON r1.user_id <> r2.user_id 
      AND r1.book_id = r2.book_id
  WHERE r1.user_id = 1
  GROUP BY r2.user_id
  HAVING count(*) >= 5) as t1
 JOIN users
   ON t1.user_id = users.id
 JOIN read
   ON read.user_id = t1.user_id
 JOIN books
   ON books.id = read.book_id
 WHERE EXISTS(SELECT * 
              FROM read 
              WHERE read.user_id = 1 
                AND read.book_id = book.id)

用列表进行总结 - 按常见书籍降序排序:

SELECT users.id, users.email, t1.qty, GROUP_CONCAT(book.books)
FROM (
  SELECT r2.user_id, COUNT(*) AS qty
  FROM `read` r1
  JOIN `read` r2
    ON r1.user_id <> r2.user_id 
      AND r1.book_id = r2.book_id
  WHERE r1.user_id = 1
  GROUP BY r2.user_id
  HAVING count(*) >= 5) as t1
 JOIN users
   ON t1.user_id = users.id
 JOIN read
   ON read.user_id = t1.user_id
 JOIN books
   ON books.id = read.book_id
 WHERE EXISTS(SELECT * 
              FROM read 
              WHERE read.user_id = 1 
                AND read.book_id = book.id)
 GROUP BY users.id, users.email, t1.qty
 ORDER BY t1.qty DESC, users.email ASC