当前位置:操作系统 > Unix/Linux >>

表里存在NULL值导致not in子查询失效的问题


表里存在NULL值导致not in子查询失效的问题
 
--这是我第一次给业务写的SQL语句,查询来数据为0,当时没考虑太多就发出去了。 后业务给我看了几条测试数据,我发现确实查询结果不对。<br>--再看语句逻辑确实没错,后经过排查是BB里有几行cust_id为NULL导致not in 查询结果失效。  www.zzzyk.com  
 
<br>--改良前的语句<br>select * from AA a with(nolock)<br>where a.status_code='04'
and a.issue_date>='2012-01-01' and a.issue_date<='2012-10-30'
and LEFT(a.manorg_code,4)='8640'
and a.cust_id not in
(
    select aa.cust_id from BB aa  with(nolock) where aa.sheet_type='Q' and aa.acttype_code in ('01','02','16','19')
)<br>
 
--改良后的语句
 
select * from AA a with(nolock)
where a.status_code='04'
and a.issue_date>='2012-01-01' and a.issue_date<='2012-10-30'
and LEFT(a.manorg_code,4)='8640'
and a.cust_id not in
(
select ISNULL(aa.cust_id,'') from BB aa with(nolock) where aa.sheet_type='Q' and aa.acttype_code in ('01','02','16','19')
)
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,