我有两个表:"members_info"one_answers"member_session"。
假设在members_info中,我有字段member_id、first_name、last_name在members_class中,我有members_less_id、less_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)
它确实有效,但我不确定性能。这样可以吗?谢谢
试试这个:
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
SELECT members_infoFROM成员_信息WHERE member_id IN(从member_lessons WHERE lesson_id=6中选择member _id)AND member_id NOT IN