mysql - 无法将参数传递给存储过程

Question

我要求计算日、月和年金额的总和。我编写的存储过程如下。

DELIMITER $$

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(IN Agentid int)

BEGIN
    select (select  @DayAmount := sum(AmountRecevied) as Totoalamountperday from 
        collection_master  
        where  AgentID=Agentid and  day(Date_Time)= day(CURRENT_DATE()) 
        group by AgentID
    ) as Dayamount,

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

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

    (select @Position := @Position + 1 AS Rank  from 
        collection_master ,(SELECT @Position := 0) r
        where AgentID=Agentid
        group by AgentID
    ) as position;

END

当我执行存储过程时，我得到以下输出

当我在存储过程的单个查询中执行时，我得到正确的输出，如下

请检查一下，我哪里错了？

Answer 1

这是存储过程的前几行:

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(IN Agentid int)
BEGIN
    select (select  @DayAmount := sum(AmountRecevied) as Totoalamountperday
            from collection_master  
            where  AgentID=Agentid and  day(Date_Time)= day(CURRENT_DATE()) 
-------------------^
            group by AgentID
           ) as Dayamount,

指向的表达式是一个同义反复。也就是说，它将 AgentId 列与其自身进行比较。问题是你的参数与列同名，这是一个坏主意，坏主意，坏主意。这是一个修复:

CREATE DEFINER=`ntc`@`%` PROCEDURE `AgentRank`(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()) 
           ) as Dayamount,

另请注意，您不需要聚合。子查询必须返回零行或一行(否则会出现错误)。您仅选择一个 AgentId，因此请删除 group by 以防止误解。