方法一堆,参考吧 select id,身份证号,职业 from ( select id,身份证号,职业,row_number() over(partition by 身份证号 order by 职业) rn from T )where rn=1select * from T T1 WHERE NOT EXISTS(SELECT 1 FROM T WHERE 身份证号=T1身份证号 AND 职业<T1.职业)
select * from T WHERE (身份证号,职业) IN (SELECT 身份证号,MIN(职业) FROM T GROUP BY 身份证号)
SELECT MAX(ID)KEEP(DENSE_RANK FIRST ORDER BY 职业),身份证号,MIN(职业) FROM T GROUP BY 身份证号
select id,身份证号,职业
from (
select id,身份证号,职业,row_number() over(partition by 身份证号 order by 职业) rn
from T
)where rn=1select * from T T1
WHERE NOT EXISTS(SELECT 1 FROM T WHERE 身份证号=T1身份证号 AND 职业<T1.职业)
select * from T
WHERE (身份证号,职业) IN (SELECT 身份证号,MIN(职业) FROM T GROUP BY 身份证号)
SELECT MAX(ID)KEEP(DENSE_RANK FIRST ORDER BY 职业),身份证号,MIN(职业)
FROM T GROUP BY 身份证号