我有一个场景:
这是我的剧本:
set @p1 = '01';
set @p2 = '02';
set @p3 = '03';
set @p4 = '04';
set @p5 = '05';
set @p6 = '';
set @p7 = '07';
set @p8 = '08';
set @p9 = '09';
set @p10 = '010';
set @p11 = '11';
SET @VAL = concat_ws(' ',
IF(@p1='','',concat_ws(' ','F1=', @p1,' AND')),
IF(@p2='','',concat_ws(' ','F2=', @p2,' AND')),
IF(@p3='','',concat_ws(' ','F3=', @p3,' AND')),
IF(@p4='','',concat_ws(' ','F4=', @p4,' AND')),
IF(@p5='','',concat_ws(' ','F5=', @p5,' AND')),
IF(@p6='','',concat_ws(' ','F6=', @p6,' AND')),
IF(@p7='','',concat_ws(' ','F7=',@p7,' AND')),
IF(@p8='','',concat_ws(' ','F8=', @p8,' AND')),
IF(@p9='','',concat_ws(' ','F9=', @p9,' AND')),
IF(@p10='','',concat_ws(' ','F10=', @p10,' AND')),
IF(@p11='','',concat_ws(' ','F11=', @p11))
);
SET @res = CONCAT_WS(' ','Select ','@VAL');
PREPARE STMT FROM @res;
EXECUTE STMT;
结果:
F1= 01 AND F2= 02 AND F3= 03 AND F4= 04 AND F5= 05 AND F7= 07 AND F8= 08 AND F9= 09 AND F10= 010 AND F11= 11
此示例代码工作正常,但我的问题是以下示例:
set @p1 = '01';
set @p2 = '02';
set @p3 = '03';
set @p4 = '04';
set @p5 = '05';
set @p6 = '';
set @p7 = '07';
set @p8 = '08';
set @p9 = '09';
set @p10 = '010';
set @p11 = '';
SET @VAL = concat_ws(' ',
IF(@p1='','',concat_ws(' ','F1=', @p1,' AND')),
IF(@p2='','',concat_ws(' ','F2=', @p2,' AND')),
IF(@p3='','',concat_ws(' ','F3=', @p3,' AND')),
IF(@p4='','',concat_ws(' ','F4=', @p4,' AND')),
IF(@p5='','',concat_ws(' ','F5=', @p5,' AND')),
IF(@p6='','',concat_ws(' ','F6=', @p6,' AND')),
IF(@p7='','',concat_ws(' ','F7=',@p7,' AND')),
IF(@p8='','',concat_ws(' ','F8=', @p8,' AND')),
IF(@p9='','',concat_ws(' ','F9=', @p9,' AND')),
IF(@p10='','',concat_ws(' ','F10=', @p10,' AND')),
IF(@p11='','',concat_ws(' ','F11=', @p11))
);
SET @res = CONCAT_WS(' ','Select ','@VAL');
PREPARE STMT FROM @res;
EXECUTE STMT;
结果:
F1= 01 AND F2= 02 AND F3= 03 AND F4= 04 AND F5= 05 AND F7= 07 AND F8= 08 AND F9= 09 AND F10= 010 AND
如果最后一个字是
AND
从结果来看。
如果有更好的主意,我们将不胜感激。
