日期:2014-05-17  浏览次数:20422 次

将sql语句中的in和not in 用 exists 和 not exists 替换
SQL code
select top 42 * from View_jydhwxx where fgsbm in (101,102) and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  
and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06' 
and jydbh not in (select top 42 jydbh from View_jydhwxx 
where fgsbm in (101,102) and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06'
 and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032) 
  order by jydbh desc) order by jydbh desc


------解决方案--------------------
SQL code

--需要借助一个function来实现

create function dbo.fn_splitToTable(@str varchar(4000))
returns @objArray table
(
    obj varchar(5)
)
as
begin
    declare @xml xml
    select @xml=convert(xml,isnull(@str,'')+'<x>'+replace(@str,',','</x><x>')+'</x>')
    insert into @objArray
    select  N.c.value('.','varchar(5)')
    from @xml.nodes('/x') N(c)
    return;
end

select top 42 * 
from View_jydhwxx 
where exists(select 1 from dbo.fn_splitToTable('101,102') d where d.obj=fgsbm)
and exists(select 1 from dbo.fn_splitToTable('043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032') a where a.obj=dzzbm)
and jhrq  >= '2012-08-10 16:49:06' 
and jhrq  <= '2012-08-17 16:49:06' 
and not exists (select 1 from View_jydhwxx t 
                  where exists(select 1 from dbo.fn_splitToTable('101,102') e where e.obj=t.fgsbm)
                  and t.jhrq  >= '2012-08-10 16:49:06' 
                  and t.jhrq  <= '2012-08-17 16:49:06'
                  and exists(select 1 from dbo.fn_splitToTable('043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032') b where b.obj=t.dzzbm)
                  and t.jydbh=jydbh
                  order by jydbh desc) 
order by jydbh desc