Question

我想从用户表中的“组合-清单”一栏中找到共同的项目:

+----+--------------------+-------------------------------------+
| id | name               | following_list                      |
+----+--------------------+-------------------------------------+
|  9 | User 1             | 26,6,12,10,21,24,19,16              | 
| 10 | User 2             | 21,24                               | 
| 12 | User 3             | 9,20,21,26,30                       | 
| 16 | User 4             | 6,52,9,10                           | 
| 19 | User 5             | 9,10,6,24                           | 
| 21 | User 6             | 9,10,6,12                           | 
| 24 | User 7             | 9,10,6                              | 
| 46 | User 8             | 45                                  | 
| 52 | User 9             | 10,12,16,21,19,20,18,17,23,25,24,22 | 
+----+--------------------+-------------------------------------+

我希望能够按特定用户补贴的配对数量进行分类。例如,除第9号与第9号外,我想与所有用户匹配,看看他们共有的“后续清单”栏中哪一个识别器。

I found a way of doing this through the "SET" datatype and some bit trickery:
http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html#bits

然而,我需要在一份武断的身份证清单中这样做。我希望这完全可以通过数据库来完成,但这只是我的ague。

EDIT:感谢每个人的帮助。我仍然很想知道,以轨道为基础的办法能否奏效,但三者合力工作。

SELECT a.following_id, COUNT( c.following_id ) AS matches
FROM following a
LEFT JOIN following b ON b.user_id = a.following_id
LEFT JOIN following c ON c.user_id = a.user_id
  AND c.following_id = b.following_id
WHERE a.user_id = ?
GROUP BY a.following_id

现在我必须说服我不要过早地优化。

Answer 1

表格的正常化,将following_list 编成一个表格following:

user_id
following_id

导致容易陷入困境(未经过测试,请见):

SELECT b.user_id, COUNT(c.following)
FROM following a
JOIN following b -- get followings of <id> 
ON b.following_id = a.following_id
AND b.user_id = a.following_id
JOIN following c -- get all (other) followings of <id> again, match with followings of b
ON b.following_id = c.following_id
AND c.user_id = a.user_id
WHERE a.user_id = <id>
GROUP BY b.user_id
ORDER BY COUNT(b.following) DESC

业绩可能非常以指数和抽样为基础;数据集的大小可能会增加一个类似的栏目,定期加以更新,或仅作快速数据检索的改动。

Answer 2

If you normalised your following_list column into a separate table with user_id and follower_id, then you d find that COUNT() was extremely easy to use. You d also find the logic for selecting a list of followers, or a list of user s being followed much easier

Answer 3

如果你能够分拨<条码>,请将问题简化。 a. 在儿童表中列支,例如:

TABLE id_following_list:

id | following
--------------
10 | 21
10 | 24
46 | 45
...| ...

http://en.wikipedia.org/wiki/First_normal_form”rel=“nofollow noreferer”>。

友情链接