mysql - 错误代码 : 1137 Can't reopen table: 'amountforagents'

Question

请在下方查看我的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

我在这里尝试为 totalamountreceived 排序，以便根据 sp 中的三列总数分配排名，这三列在临时表中。但它显示错误错误代码:1137 无法重新打开表:'代理商金额

Answer 1

问题可能出在您的 UPDATE 语句内的底部子选择上。

如您所见here on the mysql docs :

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

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

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

您的 UPDATE 语句似乎并没有做很多事情。您在创建表后立即在表上使用 UPDATE(因此它将是空的)，因此无论如何都不会有任何问题。也许您只是想在那里为 @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