SELECT ROWNUM B FROM SELECT A FROM SELECT
使用者需求一個依月份及部門 執行員 購買金額排名的報表 SELECT ROWNUM, B. * FROM ( SELECT A. * FROM ( SELECT TO_CHAR(D. Order. Date, 'YYYYMM') Order. Month, E. e. Id, E. e. Name, DP. d. Name, SUM(D 1. AMOUNT) TOTAL_PRICE FROM Drink. Order D, Drink. Order. D 1, Employee E, Department DP WHERE D. Order. No = D 1. Order. No AND D 1. e. Id = E. e. Id AND E. d. Id = DP. d. Id AND D 1. deliver = 'Y' AND D. status = 'Y' GROUP BY TO_CHAR(D. Order. Date, 'YYYYMM'), E. e. Id, E. e. Name, DP. d. Name )A ORDER BY TOTAL_PRICE DESC )B 錯誤的例子
v_emp_order. Rank --列出某年度某幾個月份,依部門訂購金額前N名的員 RANK_WITHIN_MONTH <=3 CREATE or replace VIEW v_emp_order. Rank AS SELECT ROWNUM ROW_NUM, A. * FROM( SELECT TO_CHAR(D. Order. Date, 'YYYYMM') Order. Month, E. e. Id, E. e. Name, DP. d. Name, RANK() OVER (PARTITION BY DP. d. Name, TO_CHAR(D. Order. Date, 'YYYYMM') ORDER BY SUM(D 1. amount) DESC) AS RANK_WITHIN_MONTH, SUM(D 1. AMOUNT) TOTAL_PRICE FROM Drink. Order D, Drink. Order. D 1, Employee E, Department DP WHERE D. Order. No = D 1. Order. No AND D 1. e. Id = E. e. Id AND E. d. Id = DP. d. Id AND D 1. deliver = 'Y' AND D. status = 'Y' GROUP BY TO_CHAR(D. Order. Date, 'YYYYMM'), E. e. Id, E. e. Name, DP. d. Name )A 參考文件: http: //download. oracle. com/docs/cd/B 19306_01/server. 102/b 14223/analysis. htm#i 1007779
Select * from v_emp_order. Rank
Select * from v_emp_order. Rank where rank_within_month <=3;
使用技術說明 – 軟體框架 ¡ 權限框架整合 Spring Security TILES / JMESA ¡ 顯示層 Tiles JMesa Struts 2 Business Logical ¡ 控制邏輯層 Hibernate / Spring / DAO Struts 2 ¡ 資料庫管理層 Oracle Database Hibernate - ORM ¡ 資料庫系統 / Oracle Database 黃三益2008 資料庫的核心理論 與實務第四版 1 -19
系統登入相關資訊 ¡操作環境: 使用網頁瀏覽器 Internet Explorer 6/7/8, Firefox 3. x, Google Chrome ¡連線網址 : http: //61. 56. 222. 173/beverage/ ¡登入資訊 : 管理者 : admin / admin
Back up
- Slides: 32