初始数据：
select t.AGENHI, t.TACTHI, t.DTEFHI, t.DTFIHI
from mytable t
where agenhi = '81000040' ;
AGENHI TACTHI DTEFHI DTFIHI
81000040 1 24/02/92 08/03/92
81000040 1 09/03/92 28/02/93
81000040 1 01/03/93 31/05/97
81000040 0,8 01/06/97 31/12/97
81000040 1 01/01/98 31/12/98
81000040 1 01/01/99
使用此查询：
SELECT AGENHI,
DECODE(TACTHI, 0.05, '005', 0.07, '007', 0.1, '010', 0.137, '013', 0.15,
'015', 0.2, '020', 0.21, '021', 0.23, '023', 0.25, '025', 0.3, '030',
0.34, '034', 0.4, '040', 0.45, '045', 0.5, '050', 0.6, '060', 0.63,
'063',0.7, '070', 0.75, '075', 0.8,'080', 0.84, '084',0.9, '090', 1, '100',
TACTHI) as QUOTITE,
dtefhi as START_DATE,
DECODE(LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS LAST) ,null,
to_date('31122099','ddmmyyyy'), LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS
LAST)) AS END_DATE
FROM MYTABLE
WHERE AGENHI = '81000040' AND DTFIHI IS NOT NULL;
我得到：
AGENHI QUOTITE START_DATE END_DATE
81000040 100 08/03/92 28/02/93
81000040 100 28/02/93 31/05/97
81000040 100 31/05/97 31/12/97
81000040 080 31/12/97 31/12/98
81000040 100 31/12/98 31/12/99
但我需要分组“quotite”时，下一行是相同的，并显示第一行的开始日期和最后一行的日期。
预期结果：
AGENHI QUOTITE START_DATE END_DATE
81000040 100 24/02/92 31/12/97
81000040 080 01/06/97 31/12/98
81000040 100 01/01/98 31/12/99
gordon提供的解决方案：
选择agenhi,tacthi,min(dtfihi)作为start_date，lead(max(dtfihi)）over（按agenhi分区，seqnum-seqnum_2按max(dtfihi)排序）作为end_date，from（选择t.*,row_number()over（按agenhi分区，按dtfihi排序）作为seqnum，row_number()over（按agenhi分区，按dtfihi排序）作为seqnum_2 from Hia@chronos_to_s2。其中agenhi='81000040'且dtfihi不为空）t组按agenhi
结果：
81000040 1 08/03/92 null
81000040 1 31/12/98 null
81000040 0,8 31/12/97 null
如何获取end_date？如果我找到了解决办法，我会看看的，谢谢！
你有一个缺口和孤岛的问题。我将从原始数据开始，如下所示：
select agenhi, tacthi, min(dtfihi) as start_date,
lead(min(dtfihi)) over (partition by agenhi order by min(dtfihi)) as end_date
from (select t.*,
row_number() over (partition by agenhi, order by dtfihi) as seqnum,
row_number() over (partition by agenhi, tacthi order by dtfihi) as seqnum_2
from mytable t
where agenhi = '81000040' an dtfihi IS NOT NULL
) t
group by agenhi, (seqnum - seqnum_2), tacthi;
您可以添加decode()逻辑（我将把它写成case表达式），但它不会真正改变解决方案。
