FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

OVER (
  [PARTITION BY expr1, expr2, ...]   -- 將結(jié)果集水平切分為多個獨(dú)立“分區(qū)”
  ORDER BY expr3 [ASC|DESC] [, expr4 ...]  -- 在每個分區(qū)內(nèi)定義排序規(guī)則（必填?。?
  [windowing_clause]                  -- 可選：ROWS/RANGE 框定活動行范圍（如 "ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING"）
)

-- 創(chuàng)建模擬員工表（Oracle 19c+ 推薦使用 IDENTITY 列）
CREATE TABLE emp_demo (
  emp_id     NUMBER GENERATED ALWAYS AS IDENTITY,
  emp_name   VARCHAR2(50) NOT NULL,
  dept_name  VARCHAR2(30) NOT NULL,
  salary     NUMBER(10,2) NOT NULL,
  hire_date  DATE
);

-- 插入典型測試數(shù)據(jù)（含重復(fù)薪資、多部門、NULL 值場景）
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Alice', 'Engineering', 15000, DATE '2020-03-15');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Bob',   'Engineering', 18000, DATE '2019-07-22');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Charlie', 'Engineering', 18000, DATE '2021-01-10');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Diana', 'Marketing', 12000, DATE '2020-11-05');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Eve',   'Marketing', 12000, DATE '2018-09-30');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Frank', 'Marketing', 9500,  DATE '2022-02-14');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Grace', 'Sales',     16500, DATE '2019-12-01');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Henry', 'Sales',     16500, DATE '2020-05-18');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Ivy',   'Sales',     16500, DATE '2021-08-22');
INSERT INTO emp_demo (emp_name, dept_name, salary, hire_date) VALUES ('Jack',  'Sales',     14200, DATE '2017-04-12');
COMMIT;

SELECT 
  emp_name,
  dept_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn_all,
  RANK()         OVER (ORDER BY salary DESC) AS rank_all,
  DENSE_RANK()   OVER (ORDER BY salary DESC) AS dense_rank_all
FROM emp_demo
ORDER BY salary DESC;

EXPLAIN PLAN FOR
SELECT emp_name, salary, 
       ROW_NUMBER() OVER (ORDER BY salary DESC) rn 
FROM emp_demo;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN FOR
SELECT emp_name, salary, 
       RANK() OVER (ORDER BY salary DESC) rk 
FROM emp_demo;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-----------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |   220 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |            |    10 |   220 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP_DEMO   |    10 |   220 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

SELECT 
  emp_name,
  dept_name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rn_dept,
  RANK()         OVER (PARTITION BY dept_name ORDER BY salary DESC) AS rank_dept
FROM emp_demo
ORDER BY dept_name, salary DESC;

-----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |    10 |   220 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT PUSHED RANK|        |    10 |   220 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP_DEMO   |    10 |   220 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

<dependencies>
    <!-- Oracle JDBC Driver -->
    <dependency>
        <groupId>com.oracle.database.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>21.10.0.0</version>
    </dependency>
    <!-- Lombok for boilerplate reduction -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

import lombok.Data;
@Data
public class EmpRank {
    private String empName;
    private String deptName;
    private Double salary;
    private Long rowNumber;   // 注意：用 Long，非 int！Oracle NUMBER 可能超 int 范圍
    private Long rankValue;   // 同上
    // 構(gòu)造函數(shù)、toString 等略（Lombok 生成）
}

import org.springframework.stereotype.Service;
import java.sql.*;
import java.time.Instant;
import java.util.ArrayList;
import java.util.List;
@Service
public class EmpRankService {
    private static final String SQL_RANKING = """
        SELECT 
            emp_name,
            dept_name,
            salary,
            ROW_NUMBER() OVER (PARTITION BY dept_name ORDER BY salary DESC NULLS LAST) AS rn_dept,
            RANK()         OVER (PARTITION BY dept_name ORDER BY salary DESC NULLS LAST) AS rank_dept
        FROM emp_demo
        WHERE dept_name IN ?  -- 安全參數(shù)化
        ORDER BY dept_name, salary DESC
        """;
    public List<EmpRank> getDepartmentTopN(String... departments) throws SQLException {
        long start = System.nanoTime();
        List<EmpRank> result = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@//localhost:1521/ORCLPDB1", "hr", "hr");
             PreparedStatement ps = conn.prepareStatement(SQL_RANKING)) {
            // 處理 IN 子句參數(shù)（Oracle 不支持原生 List 參數(shù)，需動態(tài)拼接）
            // 生產(chǎn)環(huán)境推薦用 Oracle UDT 或臨時表，此處為演示用簡單方式
            String deptInClause = String.join(",", 
                java.util.Arrays.stream(departments)
                    .map(d -> "'" + d.replace("'", "''") + "'") // 基礎(chǔ) SQL 注入防護(hù)
                    .toList());
            String sqlWithIn = SQL_RANKING.replace("IN ?", "IN (" + deptInClause + ")");
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(sqlWithIn)) {
                while (rs.next()) {
                    EmpRank emp = new EmpRank();
                    emp.setEmpName(rs.getString("emp_name"));
                    emp.setDeptName(rs.getString("dept_name"));
                    emp.setSalary(rs.getDouble("salary")); // 注意：如果 salary 可為 NULL，用 getDouble + wasNull()
                    // 安全獲取 NUMBER 類型：先 getObject，再轉(zhuǎn) Long
                    Object rnObj = rs.getObject("rn_dept");
                    emp.setRowNumber(rnObj != null ? ((BigDecimal) rnObj).longValue() : null);
                    Object rankObj = rs.getObject("rank_dept");
                    emp.setRankValue(rankObj != null ? ((BigDecimal) rankObj).longValue() : null);
                    result.add(emp);
                }
            }
        } catch (SQLException e) {
            long durationMs = (System.nanoTime() - start) / 1_000_000;
            System.err.printf("[%s] JDBC Query failed in %d ms: %s%n", 
                Instant.now(), durationMs, e.getMessage());
            throw e;
        }
        long durationMs = (System.nanoTime() - start) / 1_000_000;
        System.out.printf("? Fetched %d ranked employees in %d ms%n", result.size(), durationMs);
        return result;
    }
}

? Fetched 10 ranked employees in 12 ms
EmpRank(empName=Bob, deptName=Engineering, salary=18000.0, rowNumber=1, rankValue=1)
EmpRank(empName=Charlie, deptName=Engineering, salary=18000.0, rowNumber=2, rankValue=1)
EmpRank(empName=Alice, deptName=Engineering, salary=15000.0, rowNumber=3, rankValue=3)
...

-- 想表達(dá)“先按部門升序，再按薪資降序”，但寫成：
ORDER BY dept_name ASC, salary DESC

ORDER BY dept_name, salary DESC  -- ASC 是默認(rèn)，可省略

UPDATE emp_demo SET dept_name = NULL WHERE emp_name = 'Frank';
COMMIT;

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
  FROM emp_demo e
) WHERE rn BETWEEN 11 AND 20;

SELECT * FROM emp_demo
ORDER BY salary DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary) rn
FROM emp_demo
WHERE rn <= 3;  -- ORA-30483: window functions are not allowed here

SELECT emp_name, salary, rn FROM (
  SELECT emp_name, salary, ROW_NUMBER() OVER (ORDER BY salary) rn
  FROM emp_demo
) WHERE rn <= 3;