SQL多条件分组过滤问题：如何筛选符合特定全量条件的id

Hey there! Let's fix that query for you. The issue with your original statement is that it only returns individual rows where order is in (1,2,3) and date is null—but it doesn't account for other rows in the same id that might violate your condition (like id=1's order 2 row with a non-null date).

To get only the ids where all their rows with order in (1,2,3) have a null date (and match your expected result of id=2), here are two solid approaches:

Approach 1: Use NOT EXISTS (intuitive and widely supported) #

This method checks that there are no rows for the same id that have order in (1,2,3) and a non-null date. We also add checks to ensure the id has all three orders (1,2,3) since your expected result only includes id=2 (which has all three):

SELECT DISTINCT t.id
FROM your_table t
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.id = t.id
      AND t2.order IN (1,2,3)
      AND t2.date IS NOT NULL
)
AND EXISTS (SELECT 1 FROM your_table t3 WHERE t3.id = t.id AND t3.order = 1)
AND EXISTS (SELECT 1 FROM your_table t4 WHERE t4.id = t.id AND t4.order = 2)
AND EXISTS (SELECT 1 FROM your_table t5 WHERE t5.id = t.id AND t5.order = 3);

Approach 2: Use GROUP BY + HAVING (concise for aggregated checks) #

We filter to only rows with order in (1,2,3), group by id, then ensure there are no non-null dates in those groups. The extra check for COUNT(DISTINCT order) = 3 ensures the id has all three orders:

SELECT id
FROM your_table
WHERE order IN (1,2,3)
GROUP BY id
HAVING COUNT(CASE WHEN date IS NOT NULL THEN 1 END) = 0
AND COUNT(DISTINCT order) = 3;

If you didn't intend to require the id to have all three orders (and wanted to include id=3 as well), just remove the extra checks for existing orders or the distinct count condition.

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