创建简单的存储过程是正确的,但我把insert select 后的代码放进去存储过程就报错了
创建table代码和存储过程代码
CREATE TABLE `orders_statistics` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`amount` int(11) NOT NULL COMMENT '数量',
`pay_money` double(10,2) NOT NULL COMMENT '应付金额',
`remainder_money` double(10,2) DEFAULT NULL COMMENT '余额抵扣金额',
`actual_pay_money` double(10,2) DEFAULT NULL COMMENT '实付金额',
`date` date DEFAULT NULL COMMENT '数据日期',
`order_type` tinyint(3) DEFAULT NULL COMMENT '订单类型:1桌码订单、2收款码订单、3外卖订单',
`source` tinyint(3) DEFAULT NULL COMMENT '下单来源 1微信小程序、2支付宝小程序',
PRIMARY KEY (`id`),
UNIQUE KEY `date_type_source_unique` (`date`,`order_type`,`source`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
DELIMITER //
CREATE PROCEDURE query_student () BEGIN
INSERT IGNORE INTO orders_statistics ( amount, pay_money, remainder_money, actual_pay_money, date, order_type, source ) SELECT
count( 1 ) amount,
sum( pay_money ) pay_money,
sum( remainder_money ) AS remainder_money,
sum( actual_pay_money ) AS actual_pay_money,
date_add( DATE( NOW()), INTERVAL - 5 DAY ) AS date,
order_type,
source
FROM
orders
WHERE
create_time > CONCAT( date_add( DATE( NOW()), INTERVAL - 1 DAY ), ' 00:00:00' )
AND concat( date_add( DATE( NOW()), INTERVAL - 1 DAY ), ' 23:59:59' )
AND STATUS = 2
GROUP BY
date,
order_type,
source
END //
是不是没有insert权限?