[已解决]sql外键表关联查询的一个问题

Question

业务背景

一个学校有很多老师
每个老师都教很多不同的班
现在要找出同时教1,2,3班的老师

环境

node.js+sequelize+mysql

表结构

• 老师表:teacher(id,name)
• 班级表:class(id,name)
• 老师班级表:teacher_class(id,teacherId,classId)

问题

sql语句怎么查呢?
在sequelize中怎么用呢?

尝试

select teacherId from teacher_class where classId in [1,2,3]
//这个连只教1班的也会查出来
select teacherId from teacher_class where classId = 1 and classId =2 and classId =3
//这个显然不可能

已解决

sql里面分组后再筛选,sequelize暂时没弄

Answer 1

#确保你的teacher_class表有unique index(teacherId, classId)
SELECT teacherId,COUNT(1) FROM teacher_class 
WHERE classId IN(1,2,3)
GROUP BY teacherId
HAVING COUNT(1)=3

Answer 2

select tt.* from(
select a.id,count(1) as cnt from teacher a
    join teacher_class b on a.id=b.teacherId
where b.classId in(1,2,3)
group by a.id
) t join teacher tt on t.id=tt.id

Answer 3

SELECT a.teacherId
FROM   teacher_class a
       INNER JOIN teacher_class b ON b.teacherId = a.teacherId AND b.classId = 2
       INNER JOIN teacher_class c ON c.teacherId = a.teacherId AND c.classId = 3
WHERE  a.classId = 1