java - 按另一个表中的最大值或最小值排序

Question

我有一个由唯一 ID 和一些其他属性组成的表。它保存着“时间表”。然后我有另一个表，其中包含每个时间表已经或将要“触发”的所有时间的列表。这不是确切的架构，但很接近:

create table schedule (
   id varchar(40) primary key,
   attr1 int,
   attr2 varchar(20)
);

create table schedule_times (
   id varchar(40) foreign key schedule(id),
   fire_date date
);

我想查询时间表，获取属性以及下一个和上一个 fire_dates，在 Java 中，有时对其中一个属性进行排序，但有时对上一个 fire_date 或下一个 fire_date 进行排序。按属性排序很容易，我只需在构建准备好的语句时将“order by”插入字符串即可。我什至不知道如何在单个查询中选择最后一个 fire_date 和下一个 fire_date - 我知道我可以通过执行

找到给定 id 的下一个 fire_date

SELECT   min(fire_date)
FROM     schedule_times
WHERE    id = ? AND
         fire_date > sysdate;

和之前的 fire_date 使用 max() 类似的事情和fire_date < sysdate 。我只是想知道如何将其合并到时间表中的单个选择中，以便我可以一次获得下一个和上一个 fire_date，以及如何按这些属性之一进行排序。

Answer 1

您可以使用 Left Join 中的两个子查询来实现这一点。
如果没有下一个/上一个计划，这样做的优点是为您的 fire_dates 返回 NULL。

Select id, attr1, attr2, next_fire_date, previous_fire_date
From schedule s
Left Join ( Select id, Min(fire_date) As next_fire_date
            From schedule_times st
            Where st.fire_date > Sysdate
            Group By id ) n
    On ( n.id = s.id )
Left Join ( Select id, Max(fire_date) As previous_fire_date
            From schedule_times st
            Where st.fire_date < Sysdate
            Group By id ) p
    On ( p.id = s.id )

然后，您可以在 next_fire_date 或 previous_fire_date 添加 ORDER BY。

<小时/>

如果性能很重要，请在 schedule_times( id, fire_date ) 上创建复合索引，这将允许子查询仅从此索引中读取。