具有公共where条件的多对多

nnt7mjpx 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(301)

+------+--------+---------+
| id   | teacher|student  |
+-------+-------+---------+
| 1    | 1      |1        |
| 2    | 3      |3        |
| 3    | 3      |4        |
| 4    | 3      |5        |
| 5    | 3      |6        |
| 6    | 4      |5        |
| 7    | 4      |6        |
+-------+-------+---------+

这是一个多对多的关联表，我如何查询一个学生列表，他们有共同的老师，例如3和4？
我希望能得到学生5和6，因为他们都“共享”同一个老师？
我现在拥有的是 SELECT ts.studentId, ts.teacherId FROM teacher_students ts group by ts.studentId, ts.teacherId having ts.teacherId in (3,4); 但是我要学生3，4，5，6而不是5，6

sql mysql many-to-many

来源：https://stackoverflow.com/questions/52325767/many-to-many-with-common-where-condition

3条答案

按热度按时间

yyyllmsg1#

使用条件聚合

select studentId from teacher_students t
 where t.teacherId in (3,4)
 group by t.studentId
 having count(distinct t.teacherId )=2

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=70b3b2de9695f2f567afeaee7ec37bda

studentId
       5
       6

赞(0）回复(0）举报 2021-06-21

2jcobegt2#

下面是一个典型的方法：

SELECT studentId
FROM teacher_students
WHERE teacherId IN (3, 4)
GROUP BY studentId
HAVING MIN(teacherId) <> MAX(teacherId);

演示

上述查询的一个优点是索引友好。一般来说，如果你想让学生有三个或三个以上的普通教师，那么你可以使用：

SELECT studentId
FROM teacher_students
WHERE teacherId IN (...)      -- some_num of teachers
GROUP BY studentId
HAVING COUNT(DISTINCT teacherId) = some_num;

赞(0）回复(0）举报 2021-06-21

dxpyg8gm3#

你也可以使用 where 带的子句 exists :

SELECT ts.*
FROM teacher_students ts
WHERE teacherId IN (3,4) AND
      EXISTS (SELECT 1 
              FROM teacher_students ts1 
              WHERE ts.studentId = ts1.studentId AND ts.teacherId <> ts1.teacherId
             );

赞(0）回复(0）举报 2021-06-21

我来回答

具有公共where条件的多对多

3条答案

演示

相关问题

热门标签

最新问答