当Between条件与表中的两条记录匹配时，如何获取最大记录？

Question

with tb as(
select 1 as PPT_FROM,40 PPT_TO from dual
union
select 15 as PPT_FROM,40 PPT_TO from dual)
select * from tb
where 40 between PPT_FROM and PPT_TO;

现在输入在两个日期范围内都是40或15，所以我想获取最大记录，即15 PPT_from和40 PPT_to

​

​

Answer 1

我觉得你想要order by和fetch

select *
from tb
where 40 between FROM and TO
order by from desc
fetch first 1 row only;

在旧版本的Oracle (12之前的版本)中，您需要一个子查询：

select tb.*
from (select tb.*
      from tb
      where 40 between FROM and TO
      order by from desc
     ) tb
where rownum = 1;

请注意，FROM和TO是非常糟糕的列名，因为它们是SQL关键字。

Answer 2

您期望的结果可以使用简单的GROUP BY语句来实现

查询

WITH
    tb
    AS
        (SELECT 1 AS PPT_FROM, 40 PPT_TO FROM DUAL
         UNION
         SELECT 15 AS PPT_FROM, 40 PPT_TO FROM DUAL)
  SELECT MAX (ppt_from) AS ppt_from, ppt_to
    FROM tb
   WHERE 40 BETWEEN PPT_FROM AND PPT_TO
GROUP BY ppt_to;

结果

PPT_FROM | PPT_TO
-----------------
15       | 40