Table1
ID Name Ordering
1 xx 5
2 yy 6我想根据Ordering的升序取得rownum来更新ordering
更新之后ID Name Ordering
1 xx 1
2 yy 2请教高手,这个Sql怎么写啊?
非常感谢,在线等...
ID Name Ordering
1 xx 5
2 yy 6我想根据Ordering的升序取得rownum来更新ordering
更新之后ID Name Ordering
1 xx 1
2 yy 2请教高手,这个Sql怎么写啊?
非常感谢,在线等...
(select num from
(select t.*,rownum num from table1 t) t2 where t2.id=t1.id)
select rwnum sn a.* from (select * from Table1 order by ordering asc) a
(SELECT RN FROM
(SELECT ROWNUM RN,T.* FROM (SELECT * FROM TABLE1 ORDER BY Ordering)T)T2
WHERE T1.ID=T2.ID);
INSERT INTO TABLE1 VALUES(1,'xx',5);
INSERT INTO TABLE1 VALUES(2,'yy',6);
INSERT INTO TABLE1 VALUES(3,'yy',3);
INSERT INTO TABLE1 VALUES(4,'yy',7);
INSERT INTO TABLE1 VALUES(5,'yy',1);
COMMIT;
SQL> select * from table1 order by Ordering; ID NAME ORDERING
---------- ---------- ----------
5 yy 1
3 yy 3
1 xx 5
2 yy 6
4 yy 7SQL> UPDATE TABLE1 T1 SET Ordering=
2 (SELECT RN FROM
3 (SELECT ROWNUM RN,T.* FROM (SELECT * FROM TABLE1 ORDER BY Ordering)T)T2
4 WHERE T1.ID=T2.ID);已更新5行。SQL> select * from TABLE1; ID NAME ORDERING
---------- ---------- ----------
1 xx 3
2 yy 4
3 yy 2
4 yy 5
5 yy 1SQL> select * from table1 order by Ordering; ID NAME ORDERING
---------- ---------- ----------
5 yy 1
3 yy 2
1 xx 3
2 yy 4
4 yy 5不是这个意思吗?