mysql 带多个条件的查询方式

16lz 2021-12-11

mysql 带多个条件的查询

环境：mysql 5.7

当一个where语句中同时出现多个and 或者or时，需要将多个OR用小括号括起来再和AND进行“与”，或者将多个AND用小括号括起来再与OR进行“或”

mysql 多对多条件查询

两个表 user和role 中间表是user_role

查询用户和角色的对应关系

select res.user_name,r.role_name from(select u.user_name,ur.role_id from user as u INNER JOIN user_role as ur where u.user_id=ur.user_id) as res INNER JOIN role as r where res.role_id=r.role_id;

1，先查询出01号同学学习的课程

SELECT C FROM sc WHERE S='01'

3，现在需要将出现次数为3的编号取出

SELECT S FROM sc WHERE C in (SELECT C FROM sc WHERE S='01') GROUP BY S HAVING COUNT(S) =3

4，与student表进行连表查询，取出相关信息

SELECT a.* FROMstudent a LEFT JOIN sc bON a.S = b.S WHERE b.S in (SELECT S FROM sc WHERE C in (SELECT C FROM sc WHERE S='01') GROUP BY S HAVING COUNT(S) =3)GROUP BY a.S

更多相关文章

随机推荐