SQL单表查询如何在结果集内二次筛选关联指定类别的规划记录

你需要从规划、宗地两张关联表中，筛选出仅关联指定两类宗地中某一类、不同时关联两类的规划记录，同时需要统计现有已查出的关联第二类宗地的结果中，未关联第一类宗地的规划数量。
你原有写法存在两个问题：

• 用name字段做匹配容易出现重名导致结果不准确，建议用表主键objectid做关联和去重依据
• 仅判断了「存在第二类宗地」，没有排除「同时存在第一类宗地」的情况，会把同时关联两类的规划也查出来

用分组+条件聚合的方式实现，逻辑清晰且大数据量下性能优于多层嵌套IN查询。

1、查询所有仅关联单类宗地的规划记录 #

SELECT 
    pl.*,
    CASE 
        WHEN MAX(CASE WHEN p.parcelclass IN ('building strata', 'bareland strata', 'common ownership') THEN 1 ELSE 0 END) = 1 
         AND MAX(CASE WHEN p.parcelclass IN ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST') THEN 1 ELSE 0 END) = 0 THEN '仅关联第一类宗地'
        WHEN MAX(CASE WHEN p.parcelclass IN ('building strata', 'bareland strata', 'common ownership') THEN 1 ELSE 0 END) = 0 
         AND MAX(CASE WHEN p.parcelclass IN ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST') THEN 1 ELSE 0 END) = 1 THEN '仅关联第二类宗地'
    END AS relate_type
FROM parcelfabric_plans pl
LEFT JOIN parcelfabric_parcels p ON p.planid = pl.objectid
GROUP BY pl.objectid, pl.name -- 需把pl表后续需要查询的字段都加到GROUP BY后，适配数据库SQL_MODE要求
HAVING 
    NOT (
        MAX(CASE WHEN p.parcelclass IN ('building strata', 'bareland strata', 'common ownership') THEN 1 ELSE 0 END) = 1
        AND MAX(CASE WHEN p.parcelclass IN ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST') THEN 1 ELSE 0 END) = 1
    )

2、单独统计仅关联第二类宗地的规划数量 #

直接在分组逻辑基础上做计数即可，得到的结果就是你原有268983条结果中，排除了同时关联第一类宗地后的准确数量：

SELECT COUNT(*) AS only_type_b_plan_count
FROM (
    SELECT pl.objectid
    FROM parcelfabric_plans pl
    INNER JOIN parcelfabric_parcels p ON p.planid = pl.objectid
    GROUP BY pl.objectid
    HAVING 
        MAX(CASE WHEN p.parcelclass IN ('ROAD', 'SUBDIVISION', 'PARK', 'INTEREST') THEN 1 ELSE 0 END) = 1
        AND MAX(CASE WHEN p.parcelclass IN ('building strata', 'bareland strata', 'common ownership') THEN 1 ELSE 0 END) = 0
) t

• 按规划唯一标识objectid分组后，通过MAX(CASE WHEN ...)判断当前规划下是否存在指定类别的宗地，存在则返回1，不存在返回0
• HAVING子句直接过滤掉同时存在两类宗地的分组，剩下的就是仅关联单类的规划
• 用主键做分组和关联，避免字段重名导致的结果误差

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