Mysql查询成员(member)是否上过课

Question

我有 2 个表:“members_info”和“members_lesson”。

假设在members_info中我有字段member_id、first_name、last_name在members_lesson中，我有members_lesson_id、lesson_id、member_id

我想创建一个查询，在其中我可以看到已完成一课但尚未完成另一课的成员。我尝试了这个查询:

SELECT members_info.member_id
   FROM members_info
   JOIN member_lessons ON members_info.member_id = member_lessons.member_id
   WHERE member_lessons.lesson_id =6 AND  member_lessons.lesson_id !=7

但是该查询不起作用。当我只检查该成员是否完成了一个类时，它起作用，但如果我想查看该成员是否没有完成另一节课，它就不起作用。有任何想法吗？谢谢!

编辑--

我尝试过这个:

SELECT members_info
FROM members_info
WHERE member_id IN (SELECT member_id FROM member_lessons WHERE lesson_id = 6)
AND member_id NOT IN (SELECT member_id FROM member_lessons WHERE lesson_id = 7)

它确实有效，但我不确定性能。这样可以吗？谢谢!

Answer 1

试试这个:

SELECT member_id, max(lesson_id)   
FROM member_lessons ml 
group by member_id 
WHERE lesson_id in (6, 7)
having count(distinct lesson_id) = 1
    and max(lesson_id) = 6