如何基于关联表查询指定学期的成员数据？三类筛选需求

三类成员查询方案 #

针对大数据量场景，以下查询均优先考虑利用索引优化执行效率：

1. 仅参与semester 552的成员 #

筛选逻辑：存在552学期参与记录，但无553学期参与记录的成员。

方案一（NOT IN子查询）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms ON m.id = ms.member
WHERE ms.semester = 552
AND m.id NOT IN (
    SELECT member FROM member_semester WHERE semester = 553
);

方案二（LEFT JOIN，更适合大数据量）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms552 ON m.id = ms552.member AND ms552.semester = 552
LEFT JOIN member_semester ms553 ON m.id = ms553.member AND ms553.semester = 553
WHERE ms553.id IS NULL;

结果：Era（id=2）

2. 同时参与semester 552和553的成员 #

筛选逻辑：同时存在552、553学期参与记录的成员。

方案一（分组统计，适合大数据量）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms ON m.id = ms.member
WHERE ms.semester IN (552, 553)
GROUP BY m.id, m.name
HAVING COUNT(DISTINCT ms.semester) = 2;

方案二（多表JOIN）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms552 ON m.id = ms552.member AND ms552.semester = 552
JOIN member_semester ms553 ON m.id = ms553.member AND ms553.semester = 553;

结果：jaga（id=1）

3. 仅参与semester 553的成员 #

筛选逻辑：存在553学期参与记录，但无552学期参与记录的成员。

方案一（NOT IN子查询）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms ON m.id = ms.member
WHERE ms.semester = 553
AND m.id NOT IN (
    SELECT member FROM member_semester WHERE semester = 552
);

方案二（LEFT JOIN，更适合大数据量）

SELECT m.id, m.name
FROM member m
JOIN member_semester ms553 ON m.id = ms553.member AND ms553.semester = 553
LEFT JOIN member_semester ms552 ON m.id = ms552.member AND ms552.semester = 552
WHERE ms552.id IS NULL;

结果：Kali（id=3）、Sura（id=4）

内容的提问来源于stack exchange，提问作者Buddhika Athukorala