我使用这些查询来选择user_ids具有相似阅读书籍的用户。
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获得email。该用户的所有book_ids都来自阅读，但这些book_ids是 ID，我希望它显示基于book_ids的书籍中的书籍。
哇，有人能理解我写的吗？
：L）
试试这个。 希望没有语法错误。 如果不起作用，请转到 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
试试这个
SELECT r2.user_id,u1.email,b1.books
FROM `read` r1
JOIN `read` r2
ON r1.user_id <> r2.user_id AND r1.book_id = r2.book_id
JOIN `users` u1
ON r2.user_id =u1.id
JOIN `books` b1
ON r1.book_id=b1.id
WHERE r1.user_id = 1
GROUP BY r2.user_id
HAVING count(*) >= 5