SQL Server多表场景下，如何查询某列含多个特定值的行？

Hey there! I get it, hunting for the right SQL Server query to find rows where a column includes multiple specific values can be tricky—especially when you’ve already scoured Google and YouTube without luck. Let’s break this down based on common scenarios you might be dealing with, using your STUDENTS table as an example.

Scenario 1: The column stores delimited values (e.g., comma-separated) #

Suppose your STUDENTS table has a column like ENROLLED_COURSES that holds values such as 'MATH101,ENG202,PHY303', and you need to find students who are enrolled in both MATH101 and ENG202.

Option 1: Using CHARINDEX (simple but less strict) #

This works if you’re confident there’s no partial value overlap (e.g., no course code like MATH1010 that would match MATH101):

SELECT PERSON_ID, ENROLL_PERIOD, ENROLLED_COURSES
FROM STUDENTS
WHERE CHARINDEX('MATH101', ENROLLED_COURSES) > 0
  AND CHARINDEX('ENG202', ENROLLED_COURSES) > 0;

Option 2: Using STRING_SPLIT (more robust, SQL Server 2016+) #

This splits the delimited column into rows, then checks if all required values are present:

SELECT s.PERSON_ID, s.ENROLL_PERIOD, s.ENROLLED_COURSES
FROM STUDENTS s
CROSS APPLY STRING_SPLIT(s.ENROLLED_COURSES, ',') AS split_courses
WHERE split_courses.value IN ('MATH101', 'ENG202')
GROUP BY s.PERSON_ID, s.ENROLL_PERIOD, s.ENROLLED_COURSES
HAVING COUNT(DISTINCT split_courses.value) = 2; -- Match the number of specific values you need

Scenario 2: Joining two tables to find matching rows #

If you’re working with a second table (e.g., ENROLLMENTS that links students to courses via PERSON_ID), and you need to find students enrolled in all specified courses:

SELECT s.PERSON_ID, s.ENROLL_PERIOD
FROM STUDENTS s
JOIN ENROLLMENTS e ON s.PERSON_ID = e.PERSON_ID
WHERE e.COURSE_CODE IN ('MATH101', 'ENG202')
GROUP BY s.PERSON_ID, s.ENROLL_PERIOD
HAVING COUNT(DISTINCT e.COURSE_CODE) = 2; -- Again, match the number of target values

Quick note for "any of the values" #

If you actually need rows where the column includes any of the specific values (not all), it’s simpler:

SELECT PERSON_ID, ENROLL_PERIOD
FROM STUDENTS
WHERE your_target_column IN ('value1', 'value2');

Adjust the column names and values to match your actual schema, and let me know if you need tweaks for your exact use case!

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