Oracle Update TOP N Issue, 請專家解答

標籤：

大家好

上周寫了匿名方法一文，很多讀者，很高興，相信我們已經從大夥的回複中，對.NET又有了更深刻的認識.

好，現在說主題，各類資料庫都有相應更新本表top n的方案.現在我一一舉例

首先看錶結構如下：

資料庫以及表建立命令初始化資料庫語句

1 CREATE TABLE student(2 id varchar(256)   NOT NULL,3 name varchar(256)   NULL,4 age int NULL)

 

 1 insert into student values(‘001‘,‘wfg‘,20); 2 insert into student values(‘002‘,‘lxx‘,21); 3 insert into student values(‘003‘,‘wly‘,3); 4 insert into student values(‘004‘,‘jcj‘,60); 5 insert into student values(‘005‘,‘wss‘,60); 6 insert into student values(‘006‘,‘xsm‘,60); 7 insert into student values(‘007‘,‘lcf‘,60); 8 insert into student values(‘008‘,‘wjy‘,35); 9 insert into student values(‘009‘,‘hyf‘,35);10 insert into student values(‘010‘,‘lwl‘,12);

 

表格結構如下：

 

ID	Name	Age
001	wfg	20
002	lxx	21
003	wly	3
004	jcj	60
005	wss	60
006	xsm	60
007	lcf	60
008	wjy	35
009	hyf	35
010	hwl	12

需求如下：按姓名順序後，更新前５個的年齡為100歲，如何辦到.

首先說下MSSQL

 

1 --方案1失敗2 update top(5) student set age = 100  order by name;3 --方案2ok4 update  student set age = 100 where id in (select top 5 id from student order by name );5 select top(5) * from student order by name;

很成功，幹得漂亮！

接下來我們看看MYSQL呢，拭目以待...

1 update student set age = 100  order by name limit 5;2 select * from student order by name limit 5;

 

Good news, MySql幹得不錯，

接下來我們再看看oracle呢？

1 --方式１失敗2 update student set age = 100 where  rownum <5 order by name ;3 commit;4 --方式２失敗5 update student set age = 100 where id in (select id from student  where rownum<5 order by name);6 commit;7 select * from student where rownum<5 order by name;

 

目前為此，還沒有在oracle裡找到辦法，請資料庫專家指導，感謝為盼！

 

 

 

Oracle Update TOP N Issue, 請專家解答