我有一个查询在存储过程之外工作(填充dejan_output表(,但在内部则不然。为什么?
CREATE DEFINER=`admin`@`%` PROCEDURE `test_sp`()
BEGIN
declare avg_all double;
declare Zipcode text(10);
declare from1, to1, from2, to2, from3, to3, from4, to4 int;
declare Year smallint;
#######################################################################
#create output table here
DROP TABLE IF EXISTS dejan_output;
CREATE TABLE dejan_output(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Zipcode text(10),
year smallint,
month tinyint,
RevenueIn1000USD decimal(15,4),
calendarData tinyint,
processed bit
);
#######################################################################
#insert into output table here
insert into dejan_output(Zipcode, year, month, RevenueIn1000USD, processed)
select f.Zipcode, year(ReportingMonth) year, month(ReportingMonth) month, round(sum(RevenueUSD) / 1000, 4) value, 0
from monthly_performance_data f join
(
select Zipcode, year(ReportingMonth) year, count(distinct month(ReportingMonth)) count
from monthly_performance_data
group by Zipcode, year(ReportingMonth)
) d on f.Zipcode = d.Zipcode and year(ReportingMonth) = d.year and d.count = 12
group by f.Zipcode, year(ReportingMonth) , month(ReportingMonth)
order by f.Zipcode, year(ReportingMonth) , month(ReportingMonth);
END
该表已成功创建,但没有任何数据。我在MySQL Workbench 8.0上运行它。
我已经找到了原因,但我仍然不知道为什么?尽管如此,我还是会发布我的解决方案,这样其他人就可以效仿了。只需删除任何声明:
declare avg_all double;
declare Zipcode text(10);
declare from1, to1, from2, to2, from3, to3, from4, to4 int;
declare Year smallint;
因为你可以使用
set @Zipcode = 'some value'
以设置该变量。