将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))asbegin 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;endselect 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