这是假设的Twitter之类的数据库中的两个表,用户可以在其中关注其他用户。 User.name字段是唯一的。
mysql> select uID, name from User;
+-----+-------------------+
| uID | name |
+-----+-------------------+
| 1 | Alice |
| 2 | Bob |
| 5 | Iron Maiden |
| 4 | Judas Priest |
| 6 | Lesser Known Band |
| 3 | Metallica |
+-----+-------------------+
6 rows in set (0.00 sec)
mysql> select * from Follower;
+-----------+------------+
| subjectID | observerID |
+-----------+------------+
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
+-----------+------------+
7 rows in set (0.00 sec)
mysql> call newFollowSuggestionsForName('Bob');
+-------------------+
| name |
+-------------------+
| Lesser Known Band |
+-------------------+
1 row in set (0.00 sec)
我想进行一项操作,为用户X建议一个他们可能感兴趣的用户列表。我认为一种试探法可能是针对用户y遵循的所有y显示X,而X和y遵循至少3个相同的用户。下面是我想出的SQL。我的问题是,它是否可以通过其他一些方式更有效或更好地完成。
DELIMITER //
CREATE PROCEDURE newFollowSuggestionsForName(IN in_name CHAR(60))
BEGIN
DECLARE xuid INT;
SET xuid = (select uID from User where name=in_name);
select name
from User, (select subjectID
from follower
where observerID in (
select observerID
from Follower
where observerID<>xuid and subjectID in (select subjectID from Follower where observerID=xuid)
group by observerID
having count(*)>=3
)
) as T
where uID = T.subjectID and not exists (select * from Follower where subjectID=T.subjectID and observerID=xuid);
END //
DELIMITER ;
最佳答案
考虑以下用于存储过程的重构SQL代码(未经数据测试)。
select u.`name`
from `User` u
inner join
(select subf.observerID, subf.subjectID
from follower subf
where subf.observerID <> xuid
) f
on u.UID = f.subjectID
inner join
(select f1.observerID
from follower f1
inner join follower f2
on f1.subjectID = f2.subjectID
and f1.observerID <> xuid
and f2.observerID = xuid
group by f1.observerID
having count(*) >= 3
) o
on f.observerID = o.observerID