如何筛选IsDomestic列全为1的唯一PersonID记录

Based on your requirement—keeping only PersonIDs where every associated IsDomestic entry is 1, and excluding any IDs that have even one 0—here are two straightforward, efficient solutions:

Solution 1: GROUP BY + HAVING Clause #

This approach groups rows by PersonID and checks that the minimum value of IsDomestic is 1 (since if any row for an ID has 0, the min would be 0).

SELECT PersonID
FROM your_table_name
GROUP BY PersonID
HAVING MIN(IsDomestic) = 1;

How it works: #

• GROUP BY PersonID aggregates all rows for each unique person.
• The HAVING clause filters groups where the smallest IsDomestic value is 1, guaranteeing all entries for that ID are 1.

Solution 2: NOT EXISTS Subquery #

This method explicitly excludes any PersonID that has at least one 0 in IsDomestic.

SELECT DISTINCT PersonID
FROM your_table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name t2
    WHERE t2.PersonID = t1.PersonID
      AND t2.IsDomestic = 0
);

How it works: #

• The subquery finds all PersonIDs with an IsDomestic value of 0.
• The outer query selects distinct PersonIDs that don't appear in that subquery, ensuring all their IsDomestic entries are 1.

Both queries will return your expected result:

+--------+
|PersonID|
+--------+
|2       |
+--------+
|4       |
+--------+

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