gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 12:59:01 25 4
gpt4 key购买 nike

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

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

当我执行存储过程时,我得到以下输出 enter image description here

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

请检查一下,我哪里错了?

最佳答案

这是存储过程的前几行:

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 以防止误解。

关于mysql - 无法将参数传递给存储过程,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23601794/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com