你如何比较两组数字，并得到使用MySQL和PHP最相关的结果？ [英] How can you compare sets of numbers and get the most relevant results using MySQL and PHP?

问题描述

考虑一下：

set A: 1 2 3 4
set B:     3 4 5 6
set C:       4 5 6 7
set D: 1

我要与剩下的比较D和得到的结果集的最相关的数字。 结果应该是在顺序如下：4（作为D具有一个共同的编号A和4是在A和也在B和C），3（作为D具有一个共同的编号A和3是在A和B）， 2（作为D具有一个共同的编号A和2也是在A）中，然后在5，6，7

I want to compare D with the rest and get as a result a set of numbers as most relevant. The result should be in this order: 4 (as D has a common number with A and 4 is in A and also in B and C), 3 (as D has a common number with A and 3 is in A and B), 2 (as D has a common number with A and 2 is also in A), then 5, 6, 7.

有一些算法来做到这一点在PHP / MySQL的一个有效的方法？我并不想另起炉灶，也该数据库最终将有一个巨大的集数。

Is there some algorithm to do this in an efficient way in PHP/MySQL? I don't want to reinvent the wheel, and also the database would eventually have a huge number of sets..

推荐答案

在SQL中，我假设你有一个表称为套，2列，电子的元素和S为集名称。

In SQL, I'll assume you have a table called sets, with 2 columns, e for the elements and s for the set name.

select e,count(*) as c from sets where s in
(select s from sets where e in (select e from sets where s='D') group by s)
group by e order by c desc

说明：

(select e from sets where s='D')

选择D组的元素。

selects the elements of group D.

(select s from sets where e in (select e from sets where s='D') group by s)

选择所有具有与previously所选组共同的成员组。

selects all the groups that have common members with the previously selected group.

，然后选择从这些集合的所有元素，并责令其按出场次数（如乔尔建议）

and then you select all elements from these sets, and order them by the number of appearances (as joel suggested)

这篇关于你如何比较两组数字，并得到使用MySQL和PHP最相关的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！