try:
update tbname set pos=(select max(rk) from (
select t.*,rank() over(order by clicks) rk from tbname t) tt
where tt.productId=tbname.produceid)
where exists(select max(rk) from (
select t.*,rank() over(order by clicks) rk from tbname t) tt
where tt.productId=tbname.produceid);
update tbname set pos=(select max(rk) from (
select t.*,rank() over(order by clicks) rk from tbname t) tt
where tt.productId=tbname.produceid)
where exists(select max(rk) from (
select t.*,rank() over(order by clicks) rk from tbname t) tt
where tt.productId=tbname.produceid);
你试一下!
update tablename k set pos=(select t.pos from (select rownum pos,productID from tablename order by clicks desc ) t) where k.productID=t.productID;
(select rn from
(select productid,row_number() over(order by clicks desc) as rn
from ttt) aa
where aa.productid = ttt.productid);
cursor cc is select productID ,clicks from table order by clicks desc;
v_product char;
v_clicks number;
i number:=1;
begin
open cc;
fetch cc into v_product,v_clicks;
while(cc%found) loop
update table set pos=i where products=v_product;
i:=i+1;
fetch cc into v_product,v_clicks;
end loop;
commit;
close cc;
end;