当我希望 SQL 函数返回单个值时，为什么它却返回一个表？
我想编写一个函数，因为我需要在后续问题计算具有两个公交车站的公交车票价时使用最后一站值。如果下车站点值为 Null，则将使用最后一站来计算公平性。 例如，77路公交车的测试数据： stoprank 表
我希望函数返回 sid = 77 的 stopid“40129”，这样我就可以这样做this：
select fare
from busfare
where boardtstop = 'xxx' and
alightstop = if(boardstop is not null,boardstop,laststop(77))
当 alightstop 为空时，我还需要其他问题的停止值。 我希望我足够清楚，实际问题更复杂，我正在尝试简化它。
我有一个名为 stoprank 的表，其中包含以下列：stopid、sid（公交车服务 id）、stoprank。我想创建一个函数laststop，它接受公交车服务id并返回该服务的最后一站（最高停靠站）。
我编写了以下函数：
delimiter $$
create function LastStop (serviceID int)
returns int
DETERMINISTIC
begin
declare laststopid int;
set laststopid = (select stopid from stoprank where sid = serviceID and
rankorder = (select max(rankorder) from stoprank where sid = serviceID));
return laststopid;
end $$
delimiter ;
但是，不是返回一个值，而是最后一站，它返回相同值的表： 实际输出
值是正确的，但格式错误，我不明白为什么。
I want to write a function because I need the laststop value in a subsequent question calculation the bus fare with a pair of bus stops. If the alight bus stop value is Null the last stop will be used to calculate the fair.
for example, test data for bus service 77:
stoprank table
I want the function to return stopid "40129" for sid = 77 so I can do this:
select fare
from busfare
where boardtstop = 'xxx' and
alightstop = if(boardstop is not null,boardstop,laststop(77))
I also need the stop value for other questions as well when alightstop is null.
I hope I'm clear enough, the actual question is more complicated I'm trying to simplify it.
I have a table called stoprank which has columns: stopid, sid(bus service id), stoprank. I want to create a function laststop that takes in a bus service id and returns the last stop of the service(highest stoprank).
Overview of the stoprank table
I wrote the following function:
delimiter $
create function LastStop (serviceID int)
returns int
DETERMINISTIC
begin
declare laststopid int;
set laststopid = (select stopid from stoprank where sid = serviceID and
rankorder = (select max(rankorder) from stoprank where sid = serviceID));
return laststopid;
end $
delimiter ;
However, instead of returning one value, the last stop, it returns a table of the same value:
actual output
The value is correct but the format is wrong, I can't figure out why.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您应该按照这种方式编写代码。
You should write the code in this way.