IN 查询是 SQL 中用于检查某个值是否存在于指定列表中的有效方式。以下是几种编写 IN 查询来确定用户是否参加了特定项目的方法:
SELECT user_id, user_name
FROM users
WHERE user_id IN (
SELECT user_id
FROM project_participants
WHERE project_id = 特定项目ID
);
SELECT u.user_id, u.user_name
FROM users u
JOIN project_participants pp ON u.user_id = pp.user_id
WHERE pp.project_id = 特定项目ID;
SELECT user_id, user_name
FROM users
WHERE user_id IN (
SELECT user_id
FROM project_participants
WHERE project_id IN (项目ID1, 项目ID2, 项目ID3)
);
SELECT user_id, user_name
FROM users u
WHERE EXISTS (
SELECT 1
FROM project_participants pp
WHERE pp.user_id = u.user_id
AND pp.project_id = 特定项目ID
);
假设我们想知道哪些用户参加了项目ID为123的项目:
SELECT u.user_id, u.user_name, u.email
FROM users u
WHERE u.user_id IN (
SELECT participant_id
FROM project_memberships
WHERE project_id = 123
AND status = 'active' -- 可选:只检查活跃成员
);
project_id
和 user_id
字段上有适当的索引您需要根据实际的数据库结构和业务需求调整上述查询。