sql子查询的子查询内部无法使用最外层的参数的问题

本文链接：https://blog.youkuaiyun.com/wangduqiang747/article/details/117751666

本文探讨了SQL查询中常见的性能瓶颈及优化方法，包括如何通过合理使用子查询和连接操作来提高查询效率。针对具体案例提供了两种不同的实现方式，并对比了它们的执行时间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在这里插入图片描述

					(case
                         when (select distinct 1
                                 from lcinshold
                                where hangupreason in ('07', '08')
                                  and canceloperator is null
                                  and insuredid = a.insuredid) is not null then
                          /*(select ',' || wm_concat(aa)
                             from (select (select codename
                                             from ldcode
                                            where codetype = 'holdupreason'
                                              and code = hangupreason) aa
                                     from lcinshold
                                    where hangupreason in ('07', '08')
                                      and canceloperator is null
                                      and insuredid = a.insuredid))*/
                                       ( select ',' || wm_concat(aa)  from (select (select codename  from ldcode 
                                        where codetype = 'holdupreason' and code = hangupreason) aa , insuredid  from 
                                        lcinshold where hangupreason in ('07', '08') and canceloperator is null  ) 
                                        where   insuredid = a.insuredid  )
                      else
                          ' '
                      end)

注释起来的是错误的 a.insuredid 无法找到
下面是解决的.

最外层的参数无法传递到子查询的子查询.
用这种方法解决

同理

update liinscontact c
   set c.occupation =
      (select * from  (select occupation
          from lcinsured
         where effectivedate < expirydate
           and occupation is not null
           and customid = c.customid
           and employeeid = c.employeeid
           and rownum = 1
         order by effectivedate desc, modifydate desc))

         -- 需要的执行时间: 93秒

改为:

update liinscontact c
   set c.occupation =
       (select occupation
          from (select occupation,customid ,employeeid 
                  from lcinsured
                 where effectivedate < expirydate and occupation is not null 
                 order by effectivedate desc, modifydate desc)
        
         where customid = c.customid
           and employeeid = c.employeeid
           and rownum = 1
        
        ) -- 需要的执行时间: 93秒