update table set col='test' where id in(select id from (select id,rownum num,col1 from table order by col1)where num <=484 and mod(484,11)=1);
执行此语句之后再执行
update table set col='test2' where id in(select id from (select id,rownum num,col1 from table order by col1)where num <=484 and mod(484,11)=1);
这样更新的数据就产生了混乱, 第二条sql语句更新的数据有些更新到第一条sql语句当中。希望能给给答复, 到底是数据更新 排序发生了变化而使之rownum也发生了改变。
执行此语句之后再执行
update table set col='test2' where id in(select id from (select id,rownum num,col1 from table order by col1)where num <=484 and mod(484,11)=1);
这样更新的数据就产生了混乱, 第二条sql语句更新的数据有些更新到第一条sql语句当中。希望能给给答复, 到底是数据更新 排序发生了变化而使之rownum也发生了改变。
--把这个改了
select id,rownum num,col1 from table order by col1--改用row_number()
select id,row_number() over (order by col1) num,col1 from table
where id in(select id from (select id ,rownum num from (select id,col1 from table order by col1))where num <=484 and mod(num,11)=1);应该是这样的
update table set col='test'
where id in(
select id from
(select id,rownum num from
(select id,col1 from table order by col1)
) where num <=484 and mod(484,11)=1)
-- 或者分析函数
update table set col='test'
where id in(
select id from
(select id,col1,row_number() over(order by col1) rn
from table ) where rn<=484 and mod(rn,11)=1)