如何筛选some_subset_id一致、总数为2且some_parent_id一非空一空的记录

需求说明 #

需要识别出满足以下条件的记录：

• 两条记录的some_subset_id字段值相同
• 其中一条的some_parent_id有值，另一条为null
• 该some_subset_id对应的总记录数恰好为2

示例数据 #

目标结果 #

解决方案1：使用窗口函数（推荐，适合大数据量） #

利用窗口函数一次性计算每个some_subset_id的统计信息，逻辑清晰且性能优异：

WITH subset_stats AS (
    SELECT 
        *,
        COUNT(*) OVER (PARTITION BY some_subset_id) AS total_records,
        SUM(CASE WHEN some_parent_id IS NOT NULL THEN 1 ELSE 0 END) OVER (PARTITION BY some_subset_id) AS non_null_parent_count,
        SUM(CASE WHEN some_parent_id IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY some_subset_id) AS null_parent_count
    FROM your_table_name
)
SELECT 
    some_parent_id,
    some_parent_name,
    some_subset_name,
    some_subset_id,
    address
FROM subset_stats
WHERE 
    total_records = 2 
    AND non_null_parent_count = 1 
    AND null_parent_count = 1;

解决方案2：使用GROUP BY筛选+关联 #

如果数据库不支持窗口函数，可先筛选符合条件的some_subset_id，再关联原表获取完整记录：

SELECT t.*
FROM your_table_name t
JOIN (
    SELECT some_subset_id
    FROM your_table_name
    GROUP BY some_subset_id
    HAVING 
        COUNT(*) = 2 
        AND COUNT(some_parent_id) = 1 
        AND COUNT(*) - COUNT(some_parent_id) = 1
) valid_subsets ON t.some_subset_id = valid_subsets.some_subset_id;

性能优化建议 #

针对200万条数据的场景，建议创建复合索引加速查询：

CREATE INDEX idx_subset_parent ON your_table_name (some_subset_id, some_parent_id);

该索引可避免全表扫描，大幅提升分组或分区计算的效率。

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