上面少写了from select id,身份证号,职业 from ( select id,身份证号,职业,row_number() over(partition by 身份证号 order by 职业) rn from T )where rn=1 另外还有好多方法的 select * 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 缺少from 关键字吧!
select id,身份证号,职业
from (
select id,身份证号,职业,row_number() over(partition by 身份证号 order by 职业) rn
from T
)where rn=1
另外还有好多方法的
select * 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 身份证号