一对多查询，多的一方有多条记录同时满足情况的查询SQL

将这个条件写在最后，使用 having find_in_set(#{you_field},group_concat(多的一方对应的字段))；

创建一个用户表

 

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id自增主键',
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  `mobile` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '手机号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建一个学科表

create table t_subject(
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id自增主键',
  `subject_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学科名',
  `subject_desc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '学科信息',
	  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建一个学科记录表

create table t_subject_log (
	`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id自增主键',
  `subject_id` int(11)  NOT NULL COMMENT '学科id',
  `user_id` int(11)  NOT NULL COMMENT '用户id',
	  PRIMARY KEY (`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入一些记录

INSERT INTO `test`.`t_user`(`id`, `username`, `password`, `mobile`) VALUES (1000, '张三', 'zhangsan', '188');
INSERT INTO `test`.`t_user`(`id`, `username`, `password`, `mobile`) VALUES (1001, '李四', 'lisi', '199');
INSERT INTO `test`.`t_user`(`id`, `username`, `password`, `mobile`) VALUES (1002, '王五', 'wangwu', '100');
INSERT INTO `test`.`t_user`(`id`, `username`, `password`, `mobile`) VALUES (1003, '赵六', 'zhaoliu', '111');
INSERT INTO `test`.`t_user`(`id`, `username`, `password`, `mobile`) VALUES (1004, '芳芳', 'fangf', '122');

INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (1, '语文', '语文课呀呀呀呀');
INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (2, '数学', '数学课呀呀呀');
INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (3, '英语', '英语课呀呀呀');
INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (4, '生物', '生物课呀呀呀');
INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (5, '地理', '地理课呀呀呀');
INSERT INTO `test`.`t_subject`(`id`, `subject_name`, `subject_desc`) VALUES (6, '政治', '政治课呀呀呀呀');

INSERT INTO `test`.`t_subject_log`( `subject_id`, `user_id`) VALUES 
(1, 1000),
(1, 1001),
(1, 1002),
(1, 1004),
(2, 1000),
(3, 1000),
(4, 1000),
(5, 1000),
(6, 1000),
(2, 1001);

查询学科记录有语文和数学的学生

select
	tu.id,
	tu.username,
	tu.mobile,
	tsl.subject_id
from t_user tu LEFT JOIN t_subject_log tsl
on tu.id = tsl.user_id
GROUP BY tu.id HAVING FIND_IN_SET(1,GROUP_CONCAT(tsl.subject_id))>0 and FIND_IN_SET(2,GROUP_CONCAT(tsl.subject_id))>0

尝试一下吧