Oracle SQL：如何查询同一Value C下Group B不同的记录

我有一张表，各字段存在层级关联（Value D隶属于Value C，Value C隶属于Group B），示例表如下：

我需要编写SQL查询，提取同一Value C对应的Group B不同的记录（即仅返回示例中的第1、2行）。但当前使用的查询语句错误返回了第5-7行（同一Value C下Group B相同的多行），语句如下：

select * 
from table1 
where value c in (select value c 
                  from table 1 
                  group by value c 
                  having count(value c) > 1) 
  and value b in (select value b 
                  from table 1 
                  group by value b 
                  having count(value b) > 1) 
  and value d is not null 
order by 
    value c;

环境说明：使用SQL Developer操作只读数据库，本地环境为Oracle 19c，在线测试环境为Oracle 18c。

正确解决方案 #

方法1：窗口函数实现

SELECT A, "Group B", "Value C", "Value D"
FROM (
    SELECT *,
           COUNT(DISTINCT "Group B") OVER (PARTITION BY "Value C") AS group_b_count
    FROM table1
    WHERE "Value D" IS NOT NULL
) filtered
WHERE group_b_count > 1
ORDER BY "Value C";

方法2：关联子查询实现

SELECT *
FROM table1 t_main
WHERE "Value D" IS NOT NULL
AND EXISTS (
    SELECT 1
    FROM table1 t_sub
    WHERE t_sub."Value C" = t_main."Value C"
      AND t_sub."Group B" != t_main."Group B"
)
ORDER BY "Value C";

逻辑说明 #

原查询的问题在于：仅筛选了出现次数大于1的Value C和Group B，但无法区分同一Value C下的Group B是否存在差异。比如Value C=103出现3次，但对应的Group B都是14，原查询误将其纳入结果。

• 方法1通过窗口函数统计每个Value C下不同Group B的数量，筛选数量大于1的行，精准匹配需求。
• 方法2通过EXISTS子查询直接检查当前行的Value C是否存在其他不同的Group B，逻辑直观且性能优异。

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