SQL条件筛选需求：筛选同时含指定列表内外值的记录

Got it, let's break down how to solve this SQL problem. You're looking for emails where the associated program values have both at least one entry in your specified list (program1, program2) and at least one entry outside that list. Here are a couple of solid approaches to get this done:

This method groups records by email and uses aggregate functions to check for the presence of both required conditions. Adjust the table and column names to match your actual schema:

SELECT email
FROM your_table_name
GROUP BY email
HAVING 
  -- Count how many programs are in the target list (needs to be at least 1)
  SUM(CASE WHEN program IN ('program1', 'program2') THEN 1 ELSE 0 END) > 0
  -- Count how many programs are NOT in the target list (also needs to be at least 1)
  AND SUM(CASE WHEN program NOT IN ('program1', 'program2') THEN 1 ELSE 0 END) > 0;

How this works: #

• We group all records by email so we can evaluate all program entries for each user.
• The first SUM(CASE...) counts how many times the program is in your target list. If it's greater than 0, that means there's at least one match.
• The second SUM(CASE...) counts entries outside the list. A value greater than 0 confirms there's at least one non-match.
• Only emails that meet both conditions are returned.

If your table has indexes on email and program, using EXISTS can be very efficient. This approach checks for the existence of both types of entries directly:

SELECT DISTINCT t1.email
FROM your_table_name t1
WHERE
  -- Check that at least one program for this email is in the target list
  EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.email = t1.email
      AND t2.program IN ('program1', 'program2')
  )
  -- Check that at least one program for this email is NOT in the target list
  AND EXISTS (
    SELECT 1
    FROM your_table_name t3
    WHERE t3.email = t1.email
      AND t3.program NOT IN ('program1', 'program2')
  );

How this works: #

• DISTINCT ensures we only get each email once, even if there are multiple matching entries.
• Each EXISTS subquery checks for the presence of one of the required conditions. When both are true, the email is included in the results.

• Replace your_table_name and program with your actual table and column names.
• If the program column can contain NULL values, the NOT IN condition will treat NULLs as unknown (so they won't be counted as "outside the list"). If you want to include NULLs as non-matching entries, adjust the second condition to:

  AND SUM(CASE WHEN program NOT IN ('program1', 'program2') OR program IS NULL THEN 1 ELSE 0 END) > 0
  

  (For the GROUP BY method) or for the EXISTS method:

  AND EXISTS (
    SELECT 1
    FROM your_table_name t3
    WHERE t3.email = t1.email
      AND (t3.program NOT IN ('program1', 'program2') OR t3.program IS NULL)
  );
  

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