在select中使用case when 查询效率非常差
mzqfk 2007-11-13 12:54:43 declare @code_type char(2),@code_order int ,@code varchar( 10 ),@code_type_du char(2)
set @code_type = 'CO'
set @code_type_du = @code_type
set @code_order = 1
set @code = '10'
select *
from
(
-- 如果在下面的select case when 中使用@code_type变量 这种执行时间达到41秒.
-- 而如果在下面的select case when 中使用常量,执行时间只要1秒
select case when @code_type = 'CO' and 1 = 1 then ltrim(rtrim( newco ))+ltrim(rtrim( si_id ))
when 'CO' = 'CO' and 1<> 1 then ltrim(rtrim( si_id ))+ltrim(rtrim( newco ))
when 'CO' = 'SI' and 1 = 1 then ltrim(rtrim( si_id ))+ltrim(rtrim( newco ))
else ltrim(rtrim( newco ))+ltrim(rtrim( si_id )) end as 'newbarcode'
from
( select '10' as 'newco' ) as newco,
( select code_id as 'si_id' from t_bd_base_code where type_no = @code_type ) as si
) as a,
(
select case 1 when 1 then barcode1 else barcode2 end as 'oldbarcode'
from t_bd_all_barcode_maz
where case 1 when 1 then barcode1 else barcode2 end like case when @code_type = 'CO' and 1 = 1 then '10' +'%'
when @code_type = 'CO' and 1 <> 1 then '%'+ '10'
when @code_type = 'SI' and 1 = 1 then '%'+ '10'
else '10' + '%' end
) as b
where a.newbarcode = b.oldbarcode
谁知道这是什么原因?