oracle查询结果同一组取最早开始时间和最晚完成时间_oracle sql 分组 取最早日期数据

1.介绍 LAST_VALUE 和 FIRST_VALUE 函数

函数的作用恰如其名，取出首尾记录值。

需要注意的是：
first_value()是取第一个值，没有异议。
last_value()默认统计范围是 rows between unbounded preceding and current row，取的是当前行的尾值；如需取当前所有环节的尾值，需要指定：rows between unbounded preceding and unbounded following

2.sql实战示例

SELECT DISTINCT LAST_VALUE(a.COMPLETE_TIME)
                         OVER (PARTITION BY a.ACTIVITY_INST_NAME
                             ORDER BY a.CREATE_TIME ROWS BETWEEN unbounded preceding AND unbounded following)  AS LAST_TIME_VALUE,
              FIRST_VALUE(a.CREATE_TIME)
                          OVER (PARTITION BY a.ACTIVITY_INST_NAME
                              ORDER BY a.CREATE_TIME ROWS BETWEEN unbounded preceding AND unbounded following) AS FIRST_TIME_VALUE,
              a.ACTIVITY_INST_NAME
FROM V_WS_NEWLOCALCIRCUIT_TASK a
WHERE 1=1
 AND A.SHEET_ID = 'ZJF-388-191016-00071'
1
2
3
4
5
6
7
8
9
10

原始数据与查询结果对比：

有木有解决电脑前你的问题，欢迎点赞和讨论~