如何查询特定区域下同时拥有P和R类型的Bin编码记录？

现有两张数据表，结构及测试数据如下：

表结构与测试数据SQL #

create table dum_table1_bin_master
(
bin_code varchar2(25),
bin_type varchar2(1),
area varchar2(25)
);


create table dum_table2_area_master
(
area varchar2(25),
area_desc varchar2(25)
);


insert into dum_table1_bin_master values ('B1','P','A1');
insert into dum_table1_bin_master values ('B1','R','A1');
insert into dum_table1_bin_master values ('B2','P','A1');
insert into dum_table1_bin_master values ('B2','P','A1');
insert into dum_table1_bin_master values ('B2','R','A2');
insert into dum_table1_bin_master values ('B2','R','A2');
insert into dum_table1_bin_master values ('B3','A','A2');
insert into dum_table1_bin_master values ('B3','A','A2');

insert into dum_table2_area_master values ('A1', 'AREA 1 DESCRIPTION');
insert into dum_table2_area_master values ('A2', 'AREA 2 DESCRIPTION');

需求：编写SQL查询，获取特定区域中同时存在bin_type为P和R的bin_code对应的所有记录。预期输出为：

B1 P A1
B1 R A1

（原因：B1在A1区域同时具备P和R两种bin_type）

可以通过以下SQL实现需求：

SELECT bm.bin_code, bm.bin_type, bm.area
FROM dum_table1_bin_master bm
WHERE (bm.bin_code, bm.area) IN (
    SELECT bin_code, area
    FROM dum_table1_bin_master
    WHERE bin_type IN ('P', 'R')
    GROUP BY bin_code, area
    HAVING COUNT(DISTINCT bin_type) = 2
)
ORDER BY bm.bin_code, bm.bin_type;

思路说明 #

1. 子查询部分：先筛选出bin_type为P或R的记录，按bin_code和area分组，通过HAVING COUNT(DISTINCT bin_type) = 2锁定同一区域下同时拥有P和R两种类型的bin_code。
2. 主查询：用子查询得到的（bin_code, area）组合匹配原表，取出这些组合对应的所有记录，最后按bin_code和bin_type排序。

如果需要关联区域描述表，可加入JOIN扩展查询：

SELECT bm.bin_code, bm.bin_type, bm.area, am.area_desc
FROM dum_table1_bin_master bm
JOIN dum_table2_area_master am ON bm.area = am.area
WHERE (bm.bin_code, bm.area) IN (
    SELECT bin_code, area
    FROM dum_table1_bin_master
    WHERE bin_type IN ('P', 'R')
    GROUP BY bin_code, area
    HAVING COUNT(DISTINCT bin_type) = 2
)
ORDER BY bm.bin_code, bm.bin_type;

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