MySQL查询以查找图中边缘的权重

问题描述

可以说我们有表格SP(sid，pid)，该表格显示学生sid与项目pid有关.现在，我想要一个mysql查询来选择至少参与一个项目的所有学生对，以及他们合作的项目数.

Lets say we have the table SP(sid, pid) which shows student sid is involved in project pid.Now I want a mysql query to select all the pairs of students who involved at least in one project, and the number of projects that they collaborate in.

例如:
输入:
S1 P1
S1 P2
S2 P1
S3   P1
S4 P1
S4 P2

For example:
input:
S1    P1
S1    P2
S2    P1
S3    P1
S4    P1
S4    P2

输出:

S1  S2   1
S1   S3  1
S1  S4   2
S2   S3  1
S2  S4   1
S3   S4  1

S1   S2  1
S1   S3  1
S1   S4  2
S2   S3  1
S2   S4  1
S3   S4  1

推荐答案

Eric的答案可能会有所改善:

Eric's answer might be a bit improved:

select
    sp1.sid as sid1,
    sp2.sid as sid2,
    count(*) as num
from
    sp as sp1
    inner join sp as sp2 on sp1.pid=sp2.pid and sp1.sid<sp2.sid
group by sp1.sid, sp2.sid
;

使用<而不是<>以避免得到(1,4,2)AND(4,1,2)

Use < instead of <> to avoid geting (1,4,2) AND (4,1,2)

这篇关于MySQL查询以查找图中边缘的权重的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！