Oracle：将所有数据按最大和最小分组在一行中
问题描述 投票：0回答：1
我有此数据RESERVATION PREFIX FLIGHT ORIGIN DESTINATION DATE_FLIGHT
--------------------------------------------------------------
111 LA 123 LAX MIA 2020-02-01 00:00
111 LA 122 MIA SCL 2020-02-01 10:30
111 LA 667 MIA SCL 2020-02-03 14:15
111 LA 882 SCL ARG 2020-02-03 16:00
111 LA 111 SCL ARG 2020-02-03 23:00
111 LA 966 SCL ARG 2020-02-03 23:30
111 LA 622 SCL ARG 2020-02-05 08:00
我需要过滤所有信息，只将第一排和最后一排的数据留在一行中RESERVATION PREFIX_min FLIGHT_min ORIGIN_min DESTINATION_min DATE_FLIGHT_min PREFIX_max FLIGHT_max ORIGIN_max DESTINATION_max DATE_FLIGHT_max
-------------------------------------------------------------------------------------------------------------------------------------------------------------
111 LA 123 LAX MIA 2020-02-01 00:0 LA 622 SCL ARG 2020-02-05 08:00
oracle
1个回答
0
投票
您可以group by reservation获取最早和最晚的日期，然后将此查询加入表的2个副本：select
t.RESERVATION,
t1.PREFIX PREFIX_min,
t1.FLIGHT FLIGHT_min,
t1.ORIGIN ORIGIN_min,
t1.DESTINATION DESTINATION_min,
t1.DATE_FLIGHT DATE_FLIGHT_min,
t2.PREFIX PREFIX_max,
t2.FLIGHT FLIGHT_max,
t2.ORIGIN ORIGIN_max,
t2.DESTINATION DESTINATION_max,
t2.DATE_FLIGHT DATE_FLIGHT_max
from (
select
RESERVATION,
min(DATE_FLIGHT) DATE_FLIGHT_min,
max(DATE_FLIGHT) DATE_FLIGHT_max
from tablename
group by RESERVATION
) t
inner join tablename t1 on t1.RESERVATION = t.RESERVATION AND t1.DATE_FLIGHT = t.DATE_FLIGHT_min
inner join tablename t2 on t2.RESERVATION = t.RESERVATION AND t2.DATE_FLIGHT = t.DATE_FLIGHT_max
如果没有重复的日期，则将为每个预订返回1行。参见demo。结果：> RESERVATION | PREFIX_MIN | FLIGHT_MIN | ORIGIN_MIN | DESTINATION_MIN | DATE_FLIGHT_MIN | PREFIX_MAX | FLIGHT_MAX | ORIGIN_MAX | DESTINATION_MAX | DATE_FLIGHT_MAX
> ----------: | :--------- | ---------: | :--------- | :-------------- | :--------------- | :--------- | ---------: | :--------- | :-------------- | :---------------
> 111 | LA | 123 | LAX | MIA | 2020-02-01 00:00 | LA | 622 | SCL | ARG | 2020-02-05 08:00
热门问题
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
