mysql - 错误代码:1137无法重新打开表:'amountforagents'

请在下面查看我的SP

  DELIMITER $$

     CREATE DEFINER=`ntc`@`%` PROCEDURE `new_procedure`(in v_Agentid int)
BEGIN

 select (select   @DayAmount :=sum(AmountRecevied) as Totoalamountperday from
 collection_master
where  AgentID=v_Agentid and  day(Date_Time)= day(CURRENT_DATE()) ),


 (select  @MonthAmount:=sum(AmountRecevied) as Totoalamountperday  from
collection_master
where  AgentID=v_Agentid and date_time between DATE_FORMAT(NOW() ,'%Y-%m-01') and LAST_DAY(now() - interval 0 month )),



(select  @YearAmount:= sum(AmountRecevied) as Totoalamountpermonth  from
collection_master
where AgentID=v_Agentid  and year(Date_Time) =YEAR(CURRENT_DATE())),

 (select @Position := @Position + 1 AS Rank  from
    collection_master ,(SELECT @Position := 0) r
where AgentID=v_Agentid
group by AgentID
) as position;
DROP TEMPORARY TABLE IF EXISTS amountforagents;

create TEMPORARY table   amountforagents (agentId int,DayAmount decimal,MonthAmount  decimal,YearAmount decimal,Position int,totalamountreceived decimal);
   set @agentId =v_Agentid;
  update amountforagents SET totalamountreceived = (select ifnull(DayAmount,0)+ifnull(MonthAmount,0)+ifnull(YearAmount,0)  from amountforagents where agentId=v_Agentid);

 INSERT Into  amountforagents
    (agentId,DayAmount,MonthAmount,YearAmount,Position,totalamountreceived) values(@agentId,
@DayAmount,@MonthAmount,@YearAmount,@Position,@totalamountreceived);


 select agentId,DayAmount,MonthAmount,YearAmount,Position,totalamountreceived from amountforagents;


 END

在这里，我尝试按顺序将totalAmountUnreceived按三列的总数分配等级，如在sp中，这三列在临时表中。但它显示错误代码：1137无法重新打开表：“amountforagents”

最佳答案

问题可能出在您的UPDATE语句的底部。
正如你所读到的：
在同一个表中不能多次引用临时表
查询。例如，以下操作不起作用：

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

如果引用
在不同的
别名，即使引用出现在
功能。
你的UPDATE声明似乎并没有起到多大作用。在创建表后立即对其使用UPDATE（因此它将为空），这样就不会有任何how。或许你只是想在那里设置@totalamountreceived的变量？
尝试删除此行：

update amountforagents

然后修改SET语句以创建@totalamountreceived的变量和值：

SET @totalamountreceived = ifnull(@DayAmount, 0)
  + ifnull(@MonthAmount, 0)
  + ifnull(@YearAmount, 0);

这会给你带来你想要的结果，除非我误解了你想要达到的目标。
一起：

DELIMITER $$

     CREATE DEFINER=`ntc`@`%` PROCEDURE `new_procedure`(in v_Agentid int)
BEGIN

 select
    (select
            @DayAmount:=sum(AmountRecevied) as Totoalamountperday
        from
            collection_master
        where
            AgentID = v_Agentid
                and day(Date_Time) = day(CURRENT_DATE())),
    (select
            @MonthAmount:=sum(AmountRecevied) as Totoalamountperday
        from
            collection_master
        where
            AgentID = v_Agentid
                and date_time between DATE_FORMAT(NOW(), '%Y-%m-01') and LAST_DAY(now() - interval 0 month)),
    (select
            @YearAmount:=sum(AmountRecevied) as Totoalamountpermonth
        from
            collection_master
        where
            AgentID = v_Agentid
                and year(Date_Time) = YEAR(CURRENT_DATE())),
    (select
            @Position:=@Position + 1 AS Rank
        from
            collection_master,
            (SELECT @Position:=0) r
        where
            AgentID = v_Agentid
        group by AgentID) as position;
DROP TEMPORARY TABLE IF EXISTS amountforagents;

CREATE TEMPORARY TABLE amountforagents (agentId int,DayAmount decimal,MonthAmount  decimal,YearAmount decimal,Position int,totalamountreceived decimal);

SET @agentId = v_Agentid;
SET @totalamountreceived = ifnull(@DayAmount, 0)
  + ifnull(@MonthAmount, 0)
  + ifnull(@YearAmount, 0);

INSERT INTO amountforagents
    (agentId,DayAmount,MonthAmount,YearAmount,Position,totalamountreceived)
  VALUES(@agentId,
@DayAmount,@MonthAmount,@YearAmount,@Position,@totalamountreceived);

SELECT
    agentId,
    DayAmount,
    MonthAmount,
    YearAmount,
    Position,
    totalamountreceived
FROM
    amountforagents;

END

关于mysql - 错误代码:1137无法重新打开表:'amountforagents'，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23624938/