oracle 视图 group by,oracle SQL 错误: ORA-02014: 不能从具有 DISTINCT, GROUP BY 等的视图选择 FOR UPDATE...

错误的思路sql语句：

select rownum,a.id,a.deptno FROM (select emp.* from emp where state=1 order by deptno asc) a

where rownum <=10 for update;错误原因：

SQL 错误: ORA-02014: 不能从具有 DISTINCT, GROUP BY 等的视图选择 FOR UPDATE

02014. 00000 - "cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc."

oracle：对查询满足条件的数据上锁，正确的sql如下：

select id,deptno FROM emp

where id in

(select id from

(select id from emp

where state=1 order by deptno asc)

where rownum <=10

)

order by deptno

for update;内部排序后，取到相应的数据，但是用了in后，数据乱序，需重新order。

总结：

DISTINCT, GROUP BY，order by等不要直接在from后的子查询中，否者无法使用for update。