請問如何利用ROWNUM安排坐位編坐? |
尚未結案
|
tabobomber
一般會員 發表:8 回覆:9 積分:3 註冊:2005-04-07 發送簡訊給我 |
謝謝各位。若現在有我十六個人的晚宴,一邊坐年長的,一邊坐年輕的,應怎樣用oracle寫出那個編排script來? 我現在寫了這麼多,但接著的實在不應如何是好。謝大家幫忙一下。 Alter Table member Add SeatNumber Number;
Update member SET SeatNumber = ROWNUM; PROMPT========================================================;
PROMPT YOUNGEST PLAYER IN THE CLUB
PROMPT========================================================;
Select Surname, GivenName, dateOfBirth, SeatNumber
From (SELECT Surname, GivenName ,dateOfBirth, SeatNumber
FROM member
Order by dateOfBirth DESC)
WHERE ROWNUM <=8; PROMPT========================================================;
PROMPT OLDEST PLAYER IN THE CLUB
PROMPT========================================================;
Select Surname, GivenName, dateOfBirth, SeatNumber
From (SELECT Surname, GivenName ,dateOfBirth, SeatNumber
FROM member
Order by dateOfBirth)
WHERE ROWNUM <=8; 接著應如何用cursor or PL/SQL編排坐位? 是否應這樣?我實在攪不懂 DECLARE
v_ SeatNumber member. SeatNumber %type;
v_ dateOfBirth member. dateOfBirth %type; BEGIN
IF v_ dateOfBirth > ?. THEN
v_ SeatNumber : > 'Youngest';
ELSLF v_ dateOfBirth < ?. THEN
v_ SeatNumber : > 'Oldest' ; END IF;
DBMS_OUTPUT.PUT_LINE (??.)
END; IDEA: / SELECT DECODE(ROWNUM-min_sno,0,a......,NULL) dept,DECODE(ROWNUM-min_sno,0,1,rownum 1-min_sno) sno,a......,surname FROM
(SELECT * FROM member ORDER BY ....,.... ) a,
(
SELECT dept,MIN(rownum) min_sno FROM
(
SELECT * FROM member ORDER BY .....,surname
)
GROUP BY .............
) b
WHERE a.........=b............
/ 對不起大家。請問能否幫助一下?
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
說實在話, 希望別介意 Select Surname, GivenName, dateOfBirth, SeatNumber
From (SELECT Surname, GivenName ,dateOfBirth, SeatNumber
FROM member
Order by dateOfBirth DESC)
WHERE ROWNUM <=8; 與 SELECT Surname, GivenName ,dateOfBirth, SeatNumber
FROM member
WHERE ROWNUM <=8
Order by dateOfBirth DESC; 有何不同 ? 後續的 PL/SQL, 也是不太解耶.
|
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |