表结构

要求

表中 PRO_CODE拼接BIZ_NAME的结果在表中唯一

SQL

拼接组合字段生成子表

select ID,
       concat(PRO_CODE, BIZ_NAME) as pro_biz
from t_product
where DEPARTMENT = '2'
  and ENABLE_WATCH = '0'

将重复的行ID拼接成一行用 , 分隔

select listagg(id, ',') within group (ORDER BY pro_biz ) as id
from (
         select ID,
                concat(PRO_CODE, BIZ_NAME) as pro_biz
         from t_product
         where DEPARTMENT = '2'
           and ENABLE_WATCH = '0') p
group by p.pro_biz
having count(p.pro_biz) > 1 )

将一行id转成多行作为in的条件

select regexp_substr(idd, '[^,]+', 1, ROWNUM) iddd
from (select regexp_replace(id, '^(.*?),', '') as idd
      from (select listagg(id, ',') within group (ORDER BY pro_biz ) as id
            from (
                     select ID, concat(PRO_CODE, BIZ_NAME) as pro_biz
                     from t_product
                     where DEPARTMENT = '2'
                       and ENABLE_WATCH = '0') p
            group by p.pro_biz
            having count(p.pro_biz) > 1) p2) p3
connect by ROWNUM <= length(idd) - length(regexp_replace(idd, ',', '')) + 1;