首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

将sql话语中的in和not in 用 exists 和 not exists 替换

2012-09-17 
将sql语句中的in和not in 用 exists 和 not exists 替换SQL codeselect top 42 * from View_jydhwxx where

将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 

热点排行