求助：SQL中实现可模块化多条件筛选的关系除法查询方案

Got it, let's solve this relational division problem while keeping your query modular—so you can tweak, add, or remove criteria without rewriting the core logic. Here's a straightforward, adaptable approach:

WITH required_criteria AS (
    SELECT * FROM (
        VALUES 
            ('Big', 'Yes'),
            ('Children', 'Yes')
    ) AS t(Question, Answer)
)
SELECT a.RespondentId
FROM Answers a
JOIN required_criteria rc 
    ON a.Question = rc.Question 
    AND a.Answer = rc.Answer
GROUP BY a.RespondentId
HAVING COUNT(DISTINCT a.Question) = (SELECT COUNT(*) FROM required_criteria);

How this works (and why it's modular): #

• Define your criteria in one place: The required_criteria CTE is your control panel. Want to add a new condition (like "Gender = Female")? Just append another row to the VALUES list. Need to change an answer (e.g., switch "Big" to "No")? Edit the corresponding entry. Remove a condition? Delete the row—no changes needed to the rest of the query.
• Match respondents to criteria: We join the Answers table to our criteria set to pull all rows where a respondent meets any of the required conditions.
• Ensure full criteria match: Grouping by RespondentId lets us count how many distinct required criteria each respondent satisfied. The HAVING clause checks that this count equals the total number of criteria we defined—guaranteeing the respondent met every single condition.

For your sample data, this query will return 2 as expected, since only Respondent 2 matches both ('Big', 'Yes') and ('Children', 'Yes').

Bonus: Simplification for unique question-respondent pairs #

If you know each respondent has exactly one answer per question (which your sample data confirms), you can replace COUNT(DISTINCT a.Question) with just COUNT(*)—it’ll work the same and might run slightly faster.

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