业务背景

一个学校有很多老师
每个老师都教很多不同的班
现在要找出同时教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
//这个显然不可能

#确保你的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

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

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

明星图片

相关文章

• centos7 下mysql服务起不来的问题
• Mysql报错：The driver has not received any packets from the server
• TypeError: not enough arguments for format string, 字符串%批处理问题
• dbeaver导出mysql数据库数据时提示"还没有设置连接地址",该怎么解决……
• navicat 经常打开表一直在载入中 也不能关闭 着急解决
• Failed to remove the service