多条件下筛选含多行记录的学生数据SQL解决方案问询

现有数据表 #

1. Student表 #

2. StudentProfile表 #

筛选需求 #

• 需求1：筛选同时满足Profile_Category = 'DOB' AND Flag = 'Y'和Profile_Category = 'SubscriptionPlanHolder' AND Flag = 'Y'的学生，预期结果：StudentID 6、10、11
• 需求2：在需求1基础上，额外满足Profile_Category = 'AddressOnFile' AND Flag = 'Y'，预期结果：StudentID 10、11
• 需求3：在需求2基础上，额外满足Profile_Category = 'DoNotContact' AND Flag = 'Y'，预期结果：StudentID 10、11

尝试的SQL及问题 #

需求1尝试语句 #

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder')
AND Flag = 'Y'
GROUP BY c.StudentID
    HAVING COUNT(*) = 2

实际结果仅为6，未包含预期的10、11。

需求2尝试语句 #

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder','AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
    HAVING COUNT(*) = 3

实际结果为0，不符合预期。

需求3尝试语句 #

Select c.StudentID
FROM Student c
INNER JOIN StudentProfile p
ON p.StudentID = c.StudentID
WHERE Profile_Category IN ('DOB','SubscriptionPlanHolder', 'DoNotContact', 'AddressOnFile')
AND Flag = 'Y'
GROUP BY c.StudentID
    HAVING COUNT(*) = 4

实际结果为0，不符合预期。

问题原因 #

你之前的写法有两个核心问题：

1. 关联Student表过滤了目标数据：Student表中没有StudentID=10、11的记录，INNER JOIN Student会直接排除这两个ID，而你的预期结果包含它们，说明需求不需要关联Student表，直接基于StudentProfile筛选即可。
2. COUNT(*)无法准确统计有效分类数：如果某个学生存在重复的同分类记录，COUNT(*)会重复计数，应该用COUNT(DISTINCT Profile_Category)确保每个符合条件的分类只被统计一次。

正确SQL写法 #

需求1正确语句 #

直接从StudentProfile表筛选，确保每个学生同时拥有两个符合条件的分类：

SELECT StudentID
FROM StudentProfile
WHERE (Profile_Category = 'DOB' AND Flag = 'Y') 
   OR (Profile_Category = 'SubscriptionPlanHolder' AND Flag = 'Y')
GROUP BY StudentID
HAVING COUNT(DISTINCT Profile_Category) = 2;

执行结果：StudentID 6、10、11，符合预期。

需求2正确语句 #

SELECT StudentID
FROM StudentProfile
WHERE (Profile_Category = 'DOB' AND Flag = 'Y') 
   OR (Profile_Category = 'SubscriptionPlanHolder' AND Flag = 'Y')
   OR (Profile_Category = 'AddressOnFile' AND Flag = 'Y')
GROUP BY StudentID
HAVING COUNT(DISTINCT Profile_Category) = 3;

执行结果：StudentID 10、11，符合预期。

需求3正确语句 #

SELECT StudentID
FROM StudentProfile
WHERE (Profile_Category = 'DOB' AND Flag = 'Y') 
   OR (Profile_Category = 'SubscriptionPlanHolder' AND Flag = 'Y')
   OR (Profile_Category = 'AddressOnFile' AND Flag = 'Y')
   OR (Profile_Category = 'DoNotContact' AND Flag = 'Y')
GROUP BY StudentID
HAVING COUNT(DISTINCT Profile_Category) = 4;

执行结果：StudentID 10、11，符合预期。

另一种直观写法（EXISTS） #

如果觉得分组统计不够直观，可以用EXISTS来逐个验证条件，逻辑更清晰：

需求1的EXISTS写法

SELECT DISTINCT StudentID
FROM StudentProfile p1
WHERE Flag = 'Y' AND Profile_Category = 'DOB'
AND EXISTS (
    SELECT 1 FROM StudentProfile p2
    WHERE p2.StudentID = p1.StudentID
    AND p2.Flag = 'Y' AND p2.Profile_Category = 'SubscriptionPlanHolder'
);

需求2的EXISTS写法

SELECT DISTINCT StudentID
FROM StudentProfile p1
WHERE Flag = 'Y' AND Profile_Category = 'DOB'
AND EXISTS (
    SELECT 1 FROM StudentProfile p2
    WHERE p2.StudentID = p1.StudentID
    AND p2.Flag = 'Y' AND p2.Profile_Category = 'SubscriptionPlanHolder'
)
AND EXISTS (
    SELECT 1 FROM StudentProfile p3
    WHERE p3.StudentID = p1.StudentID
    AND p3.Flag = 'Y' AND p3.Profile_Category = 'AddressOnFile'
);

需求3的EXISTS写法

SELECT DISTINCT StudentID
FROM StudentProfile p1
WHERE Flag = 'Y' AND Profile_Category = 'DOB'
AND EXISTS (
    SELECT 1 FROM StudentProfile p2
    WHERE p2.StudentID = p1.StudentID
    AND p2.Flag = 'Y' AND p2.Profile_Category = 'SubscriptionPlanHolder'
)
AND EXISTS (
    SELECT 1 FROM StudentProfile p3
    WHERE p3.StudentID = p1.StudentID
    AND p3.Flag = 'Y' AND p3.Profile_Category = 'AddressOnFile'
)
AND EXISTS (
    SELECT 1 FROM StudentProfile p4
    WHERE p4.StudentID = p1.StudentID
    AND p4.Flag = 'Y' AND p4.Profile_Category = 'DoNotContact'
);

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