mysql - SQL 按常用值数量排序

Question

我有一个表格设置如下:

+---------------+
| resources     |
+---------------+
| id            |
| name          |
+---------------+
+-------------------+
| resource_subjects |
+-------------------+
| resource_id       |
| subject_id        |
+-------------------+

我需要做的是构建一个查询，查找两个资源之间共享主题的数量。

因此使用这样的 resource_subjects 表:

+---------------------------+
| resource_id | subject_id  |
+---------------------------+
| resource1   | 1           |
| resource1   | 2           |
| resource1   | 3           |
| resource1   | 4           |
| resource1   | 5           |
| resource2   | 1           |
| resource2   | 2           |
| resource2   | 3           |
| resource3   | 1           |
| resource3   | 4           |
+---------------------------+

我希望这个查询能给我这样的信息:

+----------------------------------------------------------+
| first_resource | second_resource | shared_subjects_count |
+----------------------------------------------------------+
| resource1      | resource2       | 3                     |
| resource1      | resource3       | 2                     |
| resource2      | resource3       | 1                     |
+----------------------------------------------------------+

为了理解这个想法，我脑海中的伪代码应该是这样的:

SELECT id AS first_resource, id AS second_resource, COUNT(number of subjects shared between first_resource and second_resource in resource_subjects table) AS shared_subjects_count ORDER BY shared_subjects_count DESC

如果有人能提供示例查询，甚至能为我指出正确的方向，那就太棒了。

谢谢

Answer 1

要创建与我使用的表格类似的表格:

CREATE TABLE resource_subjects (
res_id int(11),
sub_id int(11)
);

INSERT INTO resource_subjects VALUES 
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(2,1),
(2,2),
(2,3),
(3,1),
(3,4);

然后您可以使用的查询是:

SELECT t2.res_id 'first', t1.res_id 'second', COUNT(t1.sub_id)
FROM resource_subjects t1
JOIN resource_subjects t2 ON t1.res_id > t2.res_id AND t1.sub_id = t2.sub_id 
GROUP BY 1,2

请注意，我的 resource_id 是一个真实的 id(整数)，而不是一个字符串，它允许大于 ON 子句中的条件。