求MySQL查询语句：获取包含指定所有用户的线程ID

Let's break this down since we're dealing with a many-to-many relationship between users and threads (which means you almost certainly have a junction table linking user IDs to thread IDs—let's assume that table is named user_thread for this example; adjust the name to match your actual schema).

Core Approach #

The key idea here is to:

• Filter the junction table to only include the users we care about
• Group the results by thread ID
• Check that each group contains exactly the number of distinct users we specified (since that means the thread includes all of them)

MySQL Query #

If your target user IDs are 1, 2, 3, here's the query:

SELECT thread_id
FROM user_thread
WHERE user_id IN (1, 2, 3)
GROUP BY thread_id
HAVING COUNT(DISTINCT user_id) = 3;

Explanation:

• WHERE user_id IN (1, 2, 3): Narrows down the records to only those involving our target users
• GROUP BY thread_id: Groups all matching records by the thread they belong to
• HAVING COUNT(DISTINCT user_id) = 3: Ensures the thread has a matching record for every one of our target users (the DISTINCT is a safeguard in case a user is linked to the same thread multiple times—if your junction table has a unique constraint on (user_id, thread_id), you can omit it for a tiny performance boost)

Dynamic Input (For Application Use) #

If you're passing user IDs dynamically from an application, use a prepared statement to avoid SQL injection:

-- Prepare the statement
PREPARE get_threads_stmt FROM 
    'SELECT thread_id FROM user_thread WHERE user_id IN (?) GROUP BY thread_id HAVING COUNT(DISTINCT user_id) = ?';

-- Set your parameters (replace with actual values from your app)
SET @target_user_ids = '1,2,3';
SET @target_user_count = 3;

-- Execute the statement
EXECUTE get_threads_stmt USING @target_user_ids, @target_user_count;

-- Clean up
DEALLOCATE PREPARE get_threads_stmt;

Additional Tips #

• Replace user_thread with your actual junction table name (e.g., thread_participants)
• Adjust user_id and thread_id if your schema uses different column names
• If you need full thread details (not just the ID), join this result with your threads table:

  SELECT t.*
  FROM threads t
  JOIN (
      SELECT thread_id
      FROM user_thread
      WHERE user_id IN (1, 2, 3)
      GROUP BY thread_id
      HAVING COUNT(DISTINCT user_id) = 3
  ) matching_threads ON t.id = matching_threads.thread_id;
  

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