错误码:1172.在mysql中，结果包含不止一行

Question

我的代码返回一个错误错误码:1172。结果由多行组成当我运行以下代码时:

delimiter //
create function purchase_status(customernumber int) returns varchar(100) reads sql data
begin
declare purchase_state varchar(100);
select 
case
when amount < 25000 then "Silver" 
when amount between 25000 and 50000 then "Gold"
else "Platinum"
end as purchase_state2 into purchase_state
from payments;
return purchase_status;
end//
#drop function purchase_status;
select customernumber,customername,purchase_status(customernumber) as purchase_status
from customers;

我不知道怎么称呼它

Answer 1

如果使用into purchase_state，函数中的Select子句必须返回一行.

您可能忘记了customernumber或SUM(amount)上的WHERE条款，而不是amount。

的例子:

select 
case
when SUM(amount) < 25000 then "Silver" 
when SUM(amount) between 25000 and 50000 then "Gold"
else "Platinum"
end as purchase_state2 into purchase_state
from payments WHERE customernumber = customernumber