同表数据筛选：统计仅含ORAL子项无IV子项的MED数量

错误原因分析 #

你原查询的NOT EXISTS子查询存在两个核心问题：

1. 子查询内部使用了和外层完全相同的表别名（a、b），导致内层的a.ID无法关联到外层当前的MED记录，子查询实际是在判断「是否存在任何同时拥有ORAL和IV子项的MED」，而非当前MED是否存在IV子项，最终导致条件永远不成立，返回空结果。
2. 子查询逻辑冗余，不需要同时关联ORAL和IV子项，只需检查当前MED是否存在IV类型的子项即可。

正确查询方案 #

方案1：关联外层的NOT EXISTS（推荐） #

在NOT EXISTS子查询中直接关联外层的a.ID，精准检查当前MED是否不存在IV子项：

SELECT COUNT(DISTINCT a.ID)
FROM Table1 a
JOIN Table1 b ON a.ID = b.PARENT_ID
WHERE a.TYPE = 'MED' 
  AND b.TYPE = 'ORAL'
  AND NOT EXISTS (
    SELECT 1
    FROM Table1 c
    WHERE c.PARENT_ID = a.ID 
      AND c.TYPE = 'IV'
  )

方案2：LEFT JOIN + IS NULL #

通过左连接IV子项，筛选出未匹配到IV子项的MED记录：

SELECT COUNT(DISTINCT a.ID)
FROM Table1 a
JOIN Table1 b ON a.ID = b.PARENT_ID AND b.TYPE = 'ORAL'
LEFT JOIN Table1 c ON a.ID = c.PARENT_ID AND c.TYPE = 'IV'
WHERE a.TYPE = 'MED'
  AND c.ID IS NULL

方案3：分组后过滤 #

先按MED的ID分组，统计其子项类型的分布，再筛选出包含ORAL但不包含IV的分组：

SELECT COUNT(*)
FROM (
  SELECT a.ID
  FROM Table1 a
  JOIN Table1 b ON a.ID = b.PARENT_ID
  WHERE a.TYPE = 'MED'
  GROUP BY a.ID
  HAVING SUM(CASE WHEN b.TYPE = 'ORAL' THEN 1 ELSE 0 END) > 0
     AND SUM(CASE WHEN b.TYPE = 'IV' THEN 1 ELSE 0 END) = 0
) AS filtered_meds

说明 #

以上三种方案均可正确统计出ID为1、6、8的3条MED记录。其中方案1和方案2在多数数据库中性能更优，若表上存在PARENT_ID和TYPE的联合索引，查询效率会进一步提升。

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