如何限制返回结果

Question

我的查询返回重复的行，而我只想返回一行。

select  papf.Full_Name,
          papf.Employee_number,
          papf.DatE_OF_BIRTH,
          peef.last_update_date
        /*,
        petf1.ELEMENT_NAME as MedicalSchemeName*/
from per_all_people_f papf
    ,per_all_assignments_f paaf
    ,PAY_ELEMENT_ENTRIES_F peef
    ,pay_element_types_f petf
    --,fnd_user fnu    
    where (papf.Employee_number) not in
(select 
        papf1.Employee_number

from per_all_people_f papfinner
    ,per_all_assignments_f paafinner
    ,PAY_ELEMENT_ENTRIES_F peefinner
    ,pay_element_types_f petfinner

where   paafinner.person_id = papfinner.person_id
and     peefinner.assignment_id = paafinner.assignment_id
and     peefinner.element_type_id = petfinner.element_type_id

and     upper(petf1.ELEMENT_NAME) like '%Condition%'

and     SYSDATE BETWEEN  papfinner.EFFECTIVE_START_DATE AND    papfinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  paafinner.EFFECTIVE_START_DATE AND    paafinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  peefinner.EFFECTIVE_START_DATE AND    peefinner.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  petfinner.EFFECTIVE_START_DATE AND    petfinner.EFFECTIVE_END_DATE
)
and     SYSDATE BETWEEN  papf.EFFECTIVE_START_DATE AND    papf.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  paaf.EFFECTIVE_START_DATE AND    paaf.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  peef.EFFECTIVE_START_DATE AND    peef.EFFECTIVE_END_DATE
and     SYSDATE BETWEEN  petf.EFFECTIVE_START_DATE AND    petf.EFFECTIVE_END_DATE
and     upper(petf.ELEMENT_NAME) not like '%Condition%'
and     rownum <= 10000
order by peef.last_update_date;
 ....

此查询工作正常，并返回正确的结果，但结果是重复的。我只需要一个唯一的行

Answer 1

您正在加入的一个表正在返回双精度或更多。我打赌一定是"peef“也许您只能展示MAX(last_update_date)，并将其余部分与之对比。所以只需要分组。

select  papf.Full_Name,
        papf.Employee_number,
        papf.DatE_OF_BIRTH,
        last_update_date = MAX(peef.last_update_date)
...

GROUP BY
    papf.Full_Name,
    papf.Employee_number,
    papf.DatE_OF_BIRTH