SQL需求：基于BPI_ID匹配BCA_Category与Offering_Cust_Cat全集

• 数据结构：
  
• 查询目标：判断每个BPI_ID对应的BCA_Category是否存在于该BPI_ID的所有Offering_Cust_Cat可选值中，以BPI_ID作为查询键。
• 当前SQL仅完成数据聚合并尝试生成indicator列标记匹配，但未实现“与BPI_ID对应的全部Offering_Cust_Cat值比较”的逻辑：

select 
    no.object_id bpi_id, 
    no.name bpi_name, 
    nr.reference offering, 
    nr2.reference offering_cust_cat, 
    nr3.reference bca, 
    nr4.reference bca_category,
    case 
        when nr4.reference in nr2.reference then 'BCA分类匹配'
        ELSE 'BCA分类不匹配'
    END as indicator
from nc_objects no
inner join nc_references nr on nr.object_id = no.object_id and nr.attr_id = 9126099920013458621 /* 产品服务 */
inner join nc_references nr2 on nr.reference = nr2.object_id and nr2.attr_id = 9132199382713895167 /* 客户分类 */
inner join nc_references nr3 on nr3.object_id = no.object_id and nr3.attr_id =  9149183862513001518 /* 关联产品的CA */
inner join nc_references nr4 on nr4.object_id = nr3.reference and nr4.attr_id = 7030151465013152898 /* 客户分类 */
where no.object_type_id = 9126083628313449001 /* 业务产品实例 */

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