如何将SQL查询结果合并为一行，获取MIN(KNANF)与MAX(KNEND)？

我有一张TVRAB表，数据如下：

当前执行如下SQL查询：

select knanf as start, knend as end 
from TVRAB as a 
where route = '00012' 
and ( abnum = ( select min( ABNUM ) from TVRAB where route = a~route ) 
      or abnum = ( select max( ABNUM ) from TVRAB where route = a~route ) )

得到两行结果：

需要将指定route对应的最小abnum对应的knanf和最大abnum对应的knend合并为一行输出，期望结果如下：

可以通过两种方式实现单行输出：

方法1：聚合查询结合子查询 #

select 
    (select knanf from TVRAB where route = '00012' and abnum = min(abnum)) as START,
    (select knend from TVRAB where route = '00012' and abnum = max(abnum)) as END
from TVRAB
where route = '00012'
group by route;

方法2：窗口函数（支持窗口函数的数据库适用） #

select distinct
    first_value(knanf) over (partition by route order by abnum) as START,
    last_value(knend) over (partition by route order by abnum rows between current row and unbounded following) as END
from TVRAB
where route = '00012';

说明 #

• 方法1通过分组后，分别子查询获取最小abnum对应的起点、最大abnum对应的终点，直接返回单行结果。
• 方法2利用first_value和last_value窗口函数，在分组内按abnum排序后取第一个knanf和最后一个knend，通过distinct去重得到单行结果。

内容的提问来源于stack exchange，提问作者ekekakos