萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle SELECT 語法詳解與 Top-N 分頁查詢實戰

標準 SELECT 語法

SELECT [ALL | DISTINCT] 運算式1 [, 運算式2 ...]
FROM   表1 [, 表2 ...]
[WHERE  篩選條件]
[GROUP BY 分組運算式 [HAVING 分組條件]]
[ORDER BY 運算式 [ASC | DESC]];
  • [] 為可選子句
  • GROUP BY 先分組,HAVING 過濾組
  • ORDER BY 最後排序

實戰練習:hkb_test_sore 成績表

1. 取前 5 高分

SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 5;

2. 取第 5 名 (單筆)

SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 5
MINUS
SELECT a.sore_id, a.sore
FROM (SELECT * FROM hkb_test_sore ORDER BY sore DESC) a
WHERE ROWNUM <= 4;

3. 查詢分數重複的記錄

SELECT *
FROM hkb_test_sore a
WHERE a.sore IN (
    SELECT sore FROM hkb_test_sore
    GROUP BY sore HAVING COUNT(*) = 2
);

集合運算子對比

運算子說明去重
UNION合併兩結果集是
UNION ALL合併不去重否
INTERSECT交集是
MINUS差集 (第一組減第二組)是

範例資料

hkb_test2: (a,1),(b,2),(c,3),(g,4)
hkb_test3: (a,1),(b,2),(e,3),(f,4)

UNION:       a1,b2,c3,e3,f4,g4  (6 rows)
UNION ALL:   a1,b2,c3,g4,a1,b2,e3,f4  (8 rows)
INTERSECT:   a1,b2  (2 rows)
MINUS:       c3,g4  (2 rows)

完整 Employee 範例表

CREATE TABLE Employee (
    ID VARCHAR2(4) PRIMARY KEY,
    First_Name VARCHAR2(10),
    Last_Name  VARCHAR2(10),
    Start_Date DATE,
    End_Date   DATE,
    Salary     NUMBER(8,2),
    City       VARCHAR2(10),
    Description VARCHAR2(15)
);

-- 插入 8 筆測試資料 (略)

-- 簡單投影
SELECT id, first_name, last_name FROM employee;

Oracle 實現 SELECT TOP N (分頁) 模式

SELECT col1, col2, ...
FROM (
    SELECT col1, col2, ...
    FROM table_name
    ORDER BY col1, col2 ...
)
WHERE ROWNUM <= N
ORDER BY ROWNUM ASC;  -- 可選,保證順序

原理:內層排序 → 外層 ROWNUM 截取。Oracle 12c+ 可用 FETCH FIRST N ROWS ONLY 標準語法。

copyright © 萬盛學電腦網 all rights reserved