存储过程中使用concat()遇到的问题

流年撕碎青春 2018-02-01 10:35:09
在写存储过程中使用了concat()拼接动态sql,但是在页面中报错如下:

存储过程如下:
set @p_start_date = start_date;
set @p_end_date = end_date;
set @p_server_id = server_id;

if country = 'all' then

set @v_sql= concat('select
stat_time,
SUM(pay_dru) as pay_dru,
SUM(second_day) as second_day,
SUM(third_day) as third_day,
SUM(four_day) as four_day,
SUM(five_day) as five_day,
SUM(six_day) as six_day,
SUM(seventh_day) as seventh_day,
SUM(eight_day) as eight_day,
SUM(fourteen_day) as fourteen_day,
SUM(thirtieth_day) as thirtieth_day,
SUM(first_day_price) as first_day_price,
SUM(second_day_price) as second_day_price,
SUM(third_day_price) as third_day_price,
SUM(four_day_price) as four_day_price,
SUM(fifteen_day_price) as fifteen_day_price,
SUM(fifteen_day) as fifteen_day,
SUM(five_day_price) as five_day_price,
SUM(six_day_price) as six_day_price,
SUM(seventh_day_price) as seventh_day_price,
SUM(eight_day_price) as eight_day_price,
SUM(fourteen_day_price) as fourteen_day_price,
SUM(thirtieth_day_price) as thirtieth_day_price,
SUM(ninety_day_price) as ninety_day_price,
SUM(ninety_day) as ninety_day
from stat_pay_remain where (stat_time between @p_start_date and date_add(@p_end_date, interval 1 day))
and (server_id = @p_server_id)
GROUP BY stat_time
order by stat_time desc;');
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;

ELSE

set @v_sql= concat('select
stat_time,
SUM(pay_dru) as pay_dru,
SUM(second_day) as second_day,
SUM(third_day) as third_day,
SUM(four_day) as four_day,
SUM(five_day) as five_day,
SUM(six_day) as six_day,
SUM(seventh_day) as seventh_day,
SUM(eight_day) as eight_day,
SUM(fourteen_day) as fourteen_day,
SUM(fifteen_day) as fifteen_day,
SUM(thirtieth_day) as thirtieth_day,
SUM(first_day_price) as first_day_price,
SUM(second_day_price) as second_day_price,
SUM(third_day_price) as third_day_price,
SUM(four_day_price) as four_day_price,
SUM(five_day_price) as five_day_price,
SUM(six_day_price) as six_day_price,
SUM(seventh_day_price) as seventh_day_price,
SUM(eight_day_price) as eight_day_price,
SUM(fourteen_day_price) as fourteen_day_price,
SUM(fifteen_day_price) as fifteen_day_price,
SUM(thirtieth_day_price) as thirtieth_day_price,
SUM(ninety_day_price) as ninety_day_price,
SUM(ninety_day) as ninety_day
from stat_pay_remain where (stat_time between @p_start_date and date_add(@p_end_date, interval 1 day))
and (server_id = @p_server_id)
and (country in (',country,'))
GROUP BY stat_time
order by stat_time desc;');
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
end if;

COMMIT;
不知道有没有小伙伴遇到过这个问题没有?求解决方案!
...全文
1307 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
流年撕碎青春 2018-02-07
  • 打赏
  • 举报
回复
concat()中要拼接的成员中没有为null的,这一点是很确定的。问题就是成员中没有为null的,但是返回值仍然为null。
xuzuning 2018-02-07
  • 打赏
  • 举报
回复
concat() 不允许有为空(null)的成员
流年撕碎青春 2018-02-06
  • 打赏
  • 举报
回复
因为要传入参数,所以要是动态的sql,使用concat()拼接。
深圳phper 2018-02-02
  • 打赏
  • 举报
回复
为啥要拼接sql,不可以用子查询吗,业务是什么呀

21,882

社区成员

发帖
与我相关
我的任务
社区描述
从PHP安装配置,PHP入门,PHP基础到PHP应用
社区管理员
  • 基础编程社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧