你可以使用
TRIM()
函数首先删除尾随空格,然后删除尾随空格
AND
SET @VAL = TRIM('AND' FROM TRIM(@VAL));
SET @res = CONCAT_WS(' ','Select ','@VAL');
演示:
Fiddle Demo #1
另一种方法是将数据存储到(临时)表中,并使用
GROUP_CONCAT()
聚合函数
' AND '
作为分隔符。将在where子句中筛选空值:
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 = '';
create temporary table k_v(
k varchar(10),
v varchar(10)
);
insert into k_v(k, v) values
('F1', @p1),
('F2', @p2),
('F3', @p3),
('F4', @p4),
('F5', @p5),
('F6', @p6),
('F7', @p7),
('F8', @p8),
('F9', @p9),
('F10', @p10),
('F11', @p11);
SET @VAL = (
SELECT GROUP_CONCAT(k, '= ', v SEPARATOR ' AND ')
FROM k_v
WHERE v <> ''
);
SET @res = CONCAT_WS(' ','Select ','@VAL');
PREPARE STMT FROM @res;
EXECUTE STMT;
演示:
Fiddle Demo #2
取决于你的数据(@p1,…@pn)可能会有更优雅的解决方案。
