sql-server - 在where子句中避免表函数？

Question

我在where子句中添加了表函数。

select cmp_id, acno_code, sl_type, sl_code, 0 op_Dr, 0 op_cr, 0 tr_Dr, sum(amount) tr_Cr                                                  
from vf_finance                    
where cmp_id =@cmp_id1         
and unitcode in (select * from UTILfn_Split( @unit_code,',') )   
and stat_code in ('AT','PR' )        
--and pc_code  in (select * from UTILfn_Split( @sba,',') )        
AND DOC_dT >=convert(datetime,@from_date,103) and doc_Dt <= convert(datetime,@to_date,103)                                                  
and amount < 0                     
GROUP BY cmp_id, acno_code, sl_type, sl_code                                                   
)  as gl                                                 
inner join ps_Accmas acc on acc.cmp_id = gl.cmp_id and acc.acno_Code = gl.acno_code                                                   
inner join ps_owner o on gl.cmp_id = o.cmp_id                                                   
left outer join view_sl_code sl on gl.cmp_id = sl.cmp_id and gl.sl_type = sl.sl_type and gl.sl_Code = sl.sl_Code                                                  
inner join ps_slType slt on gl.cmp_id = slt.cmp_id and gl.sl_Type = slt.sl_type  
where  sl.sl_type in (select * from UTILfn_Split( @sl_type,',') )     
and acc.acno_code in(select * from UTILfn_Split( @facno_code,',') )                             
group by gl.cmp_id, gl.acno_code,gl.sl_code,gl.sl_type,slt.sl_DEsc,acc.acno_DEsc, sl.sl_DEsc, o.owner_name                  
order by gl.cmp_id, gl.acno_code,gl.sl_code,gl.sl_type 

谁能建议我如何避免where子句中的功能？

标签： sql-serverdatabasesql-server-2008stored-functions

Answer 1

你可以试试这个。我将首先指出这个现有查询中的一些问题

首先unitcode in (select * from UTILfn_Split( @unit_code,',')在这里你必须使用一个列名而不是*，虽然我不知道你的功能UTILfn_Split但仍然提到列名是可取的。

对于您的查询，您可以使用inner join而不是in具有返回类型表的函数。

代替

sl.sl_type in (select * from UTILfn_Split( @sl_type,',') ) 

你可以试试这个

yourtble as sl inner join 
(select value from UTILfn_Split( @sl_type,',') as t2 
on sl.sl_type = t2.[value]     ---- here column name with t2 depends on your function, 
---what table structure is returning, in your case it is [value]