DELIMITER $$
CREATE PROCEDURE bb_HL_sum_product3
(
IN name VARCHAR(200)
)
begin
drop TABLE tmp_reasult;
declare cal1 varchar(800) ;
set cal1="
insert into tmp_reasult(产品名称,募集规模,已募额度,杠杆率,成立时间)
select product_name,project_money,PROJECT_ORDER_MONEY,extend8,setup_date
from t_product where delete_flag=0
"
if(name<>"" )
begin
#产品名称
set SQL_FOR_SELECT= concat(SQL_FOR_SELECT," "," and product_name like ","'","%name%","'")
end
PRINT cal1
EXECUTE cal1;
EXECUTE(
'update tmp_reasult set 客户总数=tmp.s1 '
+'from t_contract_order, '
+'(select count(distinct CUSTOMER_SEED) as s1,product_name from t_contract_order '
+'group by product_name )tmp where tmp_reasult.产品名称=tmp.product_name '
)
DEALLOCATE PREPARE cal1 ;
END$$
DELIMITER ;
这是我写的存储过程，但是在Navicat里面报错，估计是关于定义变量，进行拼装语句的写法有错了。请大神帮忙看看
update tmp_reasult set 客户总数=tmp.s1 from t_contract_order,
(select count(distinct CUSTOMER_SEED) as s1,product_name from t_contract_order
group by product_name )tmp where tmp_reasult.产品名称=tmp.product_name
mysql 不支持 update 子查询更新，你用这个试试：
update tmp_reasult a inner join t_contract_order b on a.产品名称 = b.product_name
set a.客户总数 = b.s1 where a.产品名称 = b.product_name