如何在sql中获取表中一个日期的最大值

Question

我写了下面的代码-

select 
        PAPF.PERSON_NUMBER, 
        BP.NAME                                       BENEFIT_PLAN, 
        BBR.BENEFIT_RELATION_NAME, 
        Round(BPER.BNFT_AMT, 2) * 100                 COVERAGE_AMOUNT, 
        TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD')    ENROLMENTCOVSTARTDATE
        TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD')    ENROLMENTCOVSENDDATE
FROM    PER_ALL_PEOPLE_F               PAPF,
        BEN_PRTT_ENRT_RSLT             BPER, 
        BEN_PL_F                       BP, 
        BEN_BENEFIT_RELATIONS_F        BBR
        WHERE PAPF.PERSON_ID               = BPER.PERSON_ID 
        AND     BPER.PL_ID                   = BP.PL_ID 
        AND     BBR.PERSON_ID                = PAPF.PERSON_ID 
AND     BBR.BENEFIT_RELATION_NAME    = 'DFLT'
AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BP.EFFECTIVE_START_DATE   AND BP.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE  AND BBR.EFFECTIVE_END_DATE 

这会给我一个输出，就像-

PErson_number           BENEFIT_PLAN                COVERAGE_AMOUNT  ENROLMENTCOVSTARTDATE          ENROLMENTCOVSENDDATE    
1010                    US Basic PLAN               20000               20200901                    20201020
1010                    US Basic PLAN               20000               20201021                    

1011                    Us Spouse PLAN              160000              20200901                    20201020
1011                    Us Spouse PLAN              160000              20201021                    47121231

我只想为每个人检索ENROLMENTCOVSTARTDATE的最大值。预期产出应是-

PErson_number           BENEFIT_PLAN                COVERAGE_AMOUNT  ENROLMENTCOVSTARTDATE          ENROLMENTCOVSENDDATE    
1010                    US Basic PLAN               20000               20201021                    

1011                    Us Spouse PLAN              160000              20201021                    20201220

如何在主查询中使用Max？

Answer 1

首先按行的ENRT_CVG_STRT_DT对每个人进行排序(假定由PAPF.PERSON_NUMBER标识)。然后对顶部的行进行筛选。

select person_number,
       name,
       BENEFIT_PLAN,
       BENEFIT_RELATION_NAME,
       COVERAGE_AMOUNT,
       ENROLMENTCOVSTARTDATE,
       ENROLMENTCOVSENDDATE
from (
select 
        PAPF.PERSON_NUMBER, 
        BP.NAME                                       BENEFIT_PLAN, 
        BBR.BENEFIT_RELATION_NAME, 
        Round(BPER.BNFT_AMT, 2) * 100                 COVERAGE_AMOUNT, 
        TO_CHAR(BPER.ENRT_CVG_STRT_DT, 'YYYYMMDD')    ENROLMENTCOVSTARTDATE,
            TO_CHAR(BPER.ENRT_CVG_THRU_DT, 'YYYYMMDD')    ENROLMENTCOVSENDDATE,
        row_number() over (partition by PAPF.PERSON_NUMBER order by BPER.ENRT_CVG_STRT_DT desc) rn 
        from    
FROM    PER_ALL_PEOPLE_F               PAPF,
        BEN_PRTT_ENRT_RSLT             BPER, 
        BEN_PL_F                       BP, 
        BEN_BENEFIT_RELATIONS_F        BBR
        WHERE PAPF.PERSON_ID               = BPER.PERSON_ID 
        AND     BPER.PL_ID                   = BP.PL_ID 
        AND     BBR.PERSON_ID                = PAPF.PERSON_ID 
AND     BBR.BENEFIT_RELATION_NAME    = 'DFLT'
AND     SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BP.EFFECTIVE_START_DATE   AND BP.EFFECTIVE_END_DATE 
AND     SYSDATE BETWEEN BBR.EFFECTIVE_START_DATE  AND BBR.EFFECTIVE_END_DATE 
)
where rn = 1

如果您想首先接受平分，那么您将将row_number更改为秩/稠密_秩(如果您对rn =1进行筛选，这并不重要)。但是，人们会假设每个ENROLMENTCOVSTARTDATE每人只有一行。