假设我有表联盟(城市,球队)和排名(城市,评级)(除了评级是真实类型外,其他都是字符串)。排名中还有一些额外的城市不一定在联赛中。我正在尝试创建一个名为closestRating的函数,该函数将接收一个城市,并在函数参数中返回其城市与该城市的评分差距最小的球队的名称(意思不是它自己),或者如果我们正在寻找的城市也在联盟中,则返回与该城市参数对应的球队名称。(曼城在联赛和排名中都被定义为varchar(20))。
我的尝试有一些非常奇怪的语法错误
CREATE FUNCTION closestRating (city varchar(20)) RETURNS VARCHAR(20) AS $$
DECLARE minimum real;
DECLARE rat real;
BEGIN
SET minimum = -1;
FOR r.city, rating IN (SELECT r.city, r.rating FROM rankings r)
LOOP
IF city = r.city
BEGIN
SET rat = rating;
END IF;
END LOOP;
FOR rating IN (SELECT r.city, r.rating FROM rankings r, league l WHERE r.city = l.city)
LOOP
IF minimum = -1 OR minimum < ABS(rat – rating)
BEGIN
SET minimum_dist = ABS(r – rating);
SET rat = rating;
END IF;
END LOOP;
RETURN (SELECT team from rankings, league WHERE rating = rat));
END;
$func$
LANGUAGE plpgsql;
给定此数据:
create table league (
city varchar(20),
team varchar(20)
);
create table rankings (
city varchar(20),
rating real
);
insert into rankings (city, rating) values
('citya', 1),('cityb', 1.1),('cityc', 1.01),('cityd', 1.102),('citye', 1.2)
;
insert into league (city, team) values
('citya', 'teama'),('cityc', 'teamc'),('citye', 'teame')
;
此查询将返回预期输出:
with given_city as (
select rating, city
from rankings
where city = 'citya'
)
select l.team
from
league l
inner join
rankings r using (city)
cross join
given_city gc
order by abs(r.rating - gc.rating)
limit 1
;
team
-------
teama