Posts
SELECT * FROM pg_account; SELECT s.pg_id, s.pg_name, s.pg_entry, SUM(NVL(c.pg_entry,0)) cumulative, ROUND(s.pg_entry / (SELECT SUM(pg_entry) FROM pg_account)*100, 5) percentage, ROUND(SUM(NVL(c.pg_entry,0))/(SELECT SUM(pg_entry) FROM pg_account)*100,5) cumulative_percentage FROM pg_account s, pg_account c WHERE s.pg_id > c.pg_id OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry) GROUP BY s.pg_id, s.pg_name, s.pg_entry ORDER BY s.pg_id ; SELECT s.pg_id, s.pg_name, s.pg_entry, COUNT(c.pg_id) ranking FROM pg_account s, pg_account c WHERE s.pg_entry < c.pg_entry OR (s.pg_id = c.pg_id AND s.pg_entry = c.pg_entry) GROUP BY s.pg_id, s.pg_name, s.pg_entry, s.pg_entry ORDER BY s.pg_entry DESC ;
PG_ID PG_NAME PG_ENTRY ---------------------- -------------------- ---------------------- 1 Bruce 22 2 James 72 3 Yilin 65 4 Ted 77 5 Charles 35 6 Sean 43 7 Paul 57 8 Ken 35 8 個資料列已選取 PG_ID PG_NAME PG_ENTRY CUMULATIVE PERCENTAGE CUMULATIVE_PERCENTAGE ---------------------- -------------------- ---------------------- ---------------------- ---------------------- ---------------------- 1 Bruce 22 22 5.41872 5.41872 2 James 72 94 17.73399 23.15271 3 Yilin 65 159 16.00985 39.16256 4 Ted 77 236 18.96552 58.12808 5 Charles 35 271 8.62069 66.74877 6 Sean 43 314 10.59113 77.3399 7 Paul 57 371 14.03941 91.37931 8 Ken 35 406 8.62069 100 8 個資料列已選取 PG_ID PG_NAME PG_ENTRY RANKING ---------------------- -------------------- ---------------------- ---------------------- 4 Ted 77 1 2 James 72 2 3 Yilin 65 3 7 Paul 57 4 6 Sean 43 5 5 Charles 35 6 8 Ken 35 6 1 Bruce 22 8 8 個資料列已選取
