如何计算MySQL导致“多对多"关系 [英] How to count MySQL results in a has-many-through relation

问题描述

有一篇很好的文章，介绍如何过滤具有多对多关系的结果: 如何在具有多次关系

There is pretty good article how to filter results in a has-many relation: How to filter SQL results in a has-many-through relation

我只是在寻找COUNT个结果的解决方案，而不是全部显示.

I'm just seeking a solution for COUNT result, not show them all.

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

CLUB1和&&中有多少学生? CLUB2?

最好从下面的链接使用"Martin 2" 方法:

It would be great to use "Martin 2" method from a link below:

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

只需将一些内容添加到COUNT个结果中即可.

Just adding something to COUNT results.

推荐答案

查询对表student_club和club使用表别名.这允许仅针对两个俱乐部的学生返回行.然后，使用COUNT可以返回学生人数:

The query uses table aliases for tables student_club and club. This allows to return rows only for students who are in both clubs. Then, using COUNT allows to return the number of students:

SELECT COUNT(*) AS nb
FROM student s, student_club sc1, club c1, student_club sc2, club c2
WHERE s.id=sc1.student_id AND sc1.club_id=c1.id AND c1.name="CLUB1"
AND s.id=sc2.student_id AND sc2.club_id=c2.id AND c2.name="CLUB2"

如果您确实要使用"Martin 2"查询，则可以通过以下方式计算记录数:

If you really want to use the "Martin 2" query, you may count the number of records this way:

SELECT COUNT(*) AS nb
FROM (
    SELECT s.stud_id, s.name
    FROM   student s
    JOIN   student_club sc USING (stud_id)
    WHERE  sc.club_id IN (30, 50)
    GROUP  BY 1,2
    HAVING COUNT(*) > 1
) tmp;

这篇关于如何计算MySQL导致“多对多"关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！