Oracle query foreign keys contain comma separated data

Mode one 、 Comma separated fields are converted into rows for association query , When the query result comes out, it will be transferred from row to column and back

Example ：

select 
      --- staff id
      T3.id,
      -- Employee number 
      T3.staff_code,
      -- user （ Telephone ）
      T3.phone,
      -- full name 
      T3.staff_name,
      -- ID number 
      T3.USER_IDENTIFICATION,
      T3.EMAIL,
      T3.phone,
      T3.QQ,
      T3.WCHAT,
      -- Position code 
      listagg (T3.post_code, ',') 
      WITHIN GROUP (ORDER BY T3.post_code)post_code,
      -- Job title 
      listagg (T3.post_name, ',')
      WITHIN GROUP (ORDER BY T3.post_name)post_name,
      -- Furniture consultant code 
      sg.guide_code,
      -- Furniture Consultant 
      sg.guide_name,
      -- Dealer code 
      busines.business_code,
      -- Store code 
      T3.shop_code,
      -- Administrator or not 
      T3.IS_BUSINESS_ADMIN,
      -- Tenant id
      T3.TENANT_ID,
      -- state 
      T3.states,
      -- Creation time 
      to_char(T3.create_time,'yyyy-mm-dd hh24:mi:ss'),
      -- Last modified 
      to_char(T3.last_modified_time,'yyyy-mm-dd hh24:mi:ss')
     
    
      from(
            select u.id,
                   t1.TENANT_ID,
                   t1.staff_code,
                   u.phone,
                   t1.staff_name,
                   t1.USER_IDENTIFICATION,
                   t2.post_code,
                   t2.post_name,
                    t1.EMAIL,
                    t1.QQ,
                    t1.WCHAT,
                    t1.IS_BUSINESS_ADMIN,
                    u.states,
                    shop.code shop_code,
                    t1.create_time,
                    t1.last_modified_time,
                    t1.shopping_guide_user_type
               from
               fplatform.app_user u
               inner join
               (
                select
                a.*,
                REGEXP_SUBSTR(a.post_id ,'[^,]+',1,l) post_id2
                from 
                fplatform.T_BSC_STAFF a,
                (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
                WHERE l <=LENGTH(a.post_id) - LENGTH(REPLACE(post_id,','))+1  
                --and a.tenant_id='7eba602b271c49aba395a1fc264824af' and a.phone='18899998888'
                and  a.del_status=0
                -- Enable 
                --and a.IS_ONLINE=1
                order by a.staff_name

              )t1
               on t1.PLATFORM_ID=u.id
            left join fplatform.T_BSC_STAFF_POST t2
            on t1.post_id2 = t2.id
            --inner join fplatform.app_user u 
           -- on t1.PLATFORM_ID=u.id
            left join MPLATFORM.BASE_SHOP shop
            on t1.STORE_ID=shop.id
            where   u.states=1 

      )T3
      left join  fplatform.T_BSC_STAFF_GUIDE sg
      on T3.shopping_guide_user_type=sg.id
      left join fplatform.t_business_archives busines
      on T3.TENANT_ID=busines.id
      
      group by 
      T3.id,
      T3.TENANT_ID,
      T3.staff_code,
      T3.phone,
      T3.staff_name,
      T3.USER_IDENTIFICATION,
      T3.EMAIL,
      T3.phone,
      T3.QQ,
      T3.WCHAT,
      sg.guide_code,
      sg.guide_name,
      busines.business_code,
      T3.shop_code,
      T3.IS_BUSINESS_ADMIN,
      T3.states,
      T3.create_time,
      T3.last_modified_time

Mode two 、 Comma separated fields are written directly when there are few associated foreign keys case wen  Judge

Example ：

select
        --- staff id
        t2.id,
        -- Employee number 
        t1.staff_code,
        -- user （ Telephone ）
        t2.phone,
        -- full name 
        t1.staff_name,
        -- ID number 
        t1.USER_IDENTIFICATION,
        t1.EMAIL,
        t1.phone,
        t1.QQ,
        t1.WCHAT,
        -- Position code 
        CASE WHEN instr(t1.post_name,' business ')>0                    THEN '01,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Warehouse management ')>0                    THEN '02,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Home Consultant ')>0                THEN '03,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Shopowner ')>0                    THEN '04,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' The front desk ')>0                    THEN '05,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' accounting ')>0                    THEN '06,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Cashier ')>0                    THEN '07,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Installation technician ')>0                THEN '08,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Service Technician ')>0                THEN '09,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Delivery personnel ')>0                THEN '10,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' after-sales ')>0                    THEN '11,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' The designer ')>0                  THEN '12,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' The manager ')>0                    THEN '13,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Marketing Specialist ')>0                THEN '14,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Design Director ')>0                THEN '15,' ELSE  ''END||
        CASE WHEN instr(t1.post_name,' Customized business counterpart ')>0          THEN '16,' ELSE  ''END
        as post_code,
        -- Job title 
        t1.post_name,
        -- Furniture consultant code 
        t4.guide_code,
        -- Furniture Consultant 
        t4.guide_name,
        -- Dealer code 
        t5.business_code,
        -- Store code 
        t3.code shop_code,
        -- Administrator or not 
        t1.IS_BUSINESS_ADMIN,
        -- Tenant id
        t1.TENANT_ID,
        -- state 
        t2.states,
        -- Creation time 
        to_char(t1.create_time,'yyyy-mm-dd hh24:mi:ss') create_time,
        -- Last modified 
        to_char(t1.last_modified_time,'yyyy-mm-dd hh24:mi:ss') last_modified_time
              
       
        from 
          fplatform.T_BSC_STAFF t1
        inner join fplatform.app_user t2
          on t1.PLATFORM_ID=t2.id
        left join MPLATFORM.BASE_SHOP t3
          on t1.STORE_ID=t3.id
        left join  fplatform.T_BSC_STAFF_GUIDE t4
          on T1.shopping_guide_user_type=t4.id
        left join fplatform.t_business_archives t5
          on t1.TENANT_ID=t5.id
              
        where   t2.states=1