set @sql = concat("....");
PREPARE sql_real FROM @sql;
EXECUTE sql_real;
deallocate prepare sql_real;
主要是通过concat拼接SQL,然后通过prepare,execute执行SQL;
CREATE DEFINER=`root`@`%` PROCEDURE `get_saled_list`(v_keywords varchar(50))
BEGIN
declare v_list LONGTEXT;
declare v_list2 LONGTEXT;
declare v_result LONGTEXT;
declare v_where varchar(200);
select concat('concat(c.name,d.customer_name) like \'%',v_keywords,'%\'') into v_where ;
set @v_where=v_where;
DROP TABLE IF EXISTS xxx;
set @sql = concat("create table xxx as
SELECT ... ",@v_where,"AND ...");
PREPARE sql_real FROM @sql;
EXECUTE sql_real;
deallocate prepare sql_real;
SELECT GROUP_CONCAT(LEFT(col,LENGTH(col)-1)) into v_list from (
SELECT CONCAT('SUM(IF(po_no=\'',po_no,'\'',',received_qty,0)) AS ',po_no,',') col
FROM (SELECT DISTINCT po_no FROM xxx) A
) b limit 1 ;
SELECT GROUP_CONCAT(LEFT(col,LENGTH(col)-1)) into v_list2 from (
SELECT CONCAT('SUM(IF(po_no=\'',po_no,'\'',',saled_qty,0)) AS ',po_no,',') col
FROM (SELECT DISTINCT po_no FROM xxx) A
) b limit 1 ;
SET @result=CONCAT('SELECT customer_id,customer_name,goods_id,name,sum(received_qty) received_qty,',LEFT(v_list,LENGTH(v_list)-1),
',sum(saled_qty) saled_qty,',LEFT(v_list2,LENGTH(v_list2)-1),' FROM xxx GROUP BY goods_id');
-- select @result;
prepare stmt from @result;
execute stmt ;
deallocate prepare stmt;
END