MySQL的函数类型转换错误

Question

我有函数返回的员工数量整数

CREATE FUNCTION `getUser`(s CHAR(20)) RETURNS char(50) CHARSET latin1 
RETURN @user; 

当我执行它与（0.00秒）

但其执行该

select e.emp_number from hs_hr_employee e where 

hie_code_1 in (select hie_code_1 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=getUser2() and (U.def_level=1 or U.def_level=4) ) 
or 
hie_code_3 in (select hie_code_3 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=getUser2() and U.def_level=2 ) 
or 
    hie_code_4 in (select hie_code_4 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=getUser2() and U.def_level=3) 

获得更多的慢回报;

但是当我硬编码到

select e.emp_number from hs_hr_employee e where 

hie_code_1 in (select hie_code_1 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=5 and (U.def_level=1 or U.def_level=4) ) 
or 
hie_code_3 in (select hie_code_3 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=5 and U.def_level=2 ) 
or 
    hie_code_4 in (select hie_code_4 from hs_hr_emp_level L,hs_hr_users U where L.emp_number=U.emp_number and L.emp_number=5 and U.def_level=3) 

这将返回与（0.00秒）的时间。 请给出优化功能的解决方案;

Answer 1

你的样品让我的眼睛受伤了，但这里的一个可能的替代

试图

SELECT DISTINCT e.emp_number 

FROM hs_hr_employee e 

INNER JOIN hs_hr_emp_level L 
ON L.emp_number = e.emp_number 
AND (
    e.hie_code_1 = U.hie_code_1 
    OR e.hie_code_3 = U.hie_code_3 
    OR e.hie_code_4 = U.hie_code_4 
) 

INNER JOIN hs_hr_users U 
ON U.emp_number = e.emp_number 
AND U.def_level IN (1,2,3,4) 

-- You stated getUser2() returns paramter @user, so just accessing directly 
WHERE e.emp_number = @user 

假设如下

1. hs_hr_employee.emp_number == == hs_hr_emp_level.emp_numberhs_hr_emp_level.emp_number
2. hs_hr_employee.hie_code_*n* == hs_hr_users.hie_code_ n
3. getUser2()返回@user，这是一个员工号码

从上面的语句中可以看出，您的复杂查询在做什么。很难指出，因为上述感觉不正确 - 如果你不提供一个完整的表格结构/适当的细节，虽然然后我不禁进一步。