# 小议分析函数中排序对结果的影响（二）

SQL> SELECT * FROM T;

ID NAME
---------- ------------------------------
1 A
3 C
2 B

SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER(ORDER BY ID) SUM1,
4  SUM(ID) OVER(ORDER BY ID DESC) SUM2
5  FROM T;

ID NAME                                 SUM1       SUM2
---------- ------------------------------ ---------- ----------
3 C                                       6          3
2 B                                       3          5
1 A                                       1          6

SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER(ORDER BY ID DESC) SUM1,
4  SUM(ID) OVER(ORDER BY ID) SUM2
5  FROM T;

ID NAME                                 SUM1       SUM2
---------- ------------------------------ ---------- ----------
1 A                                       6          1
2 B                                       5          3
3 C                                       3          6

SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER(ORDER BY ID) SUM1,
4  SUM(ID) OVER(ORDER BY ID DESC) SUM2,
5  MAX(ID) OVER(ORDER BY ID) MAX1
6  FROM T;

ID NAME                                 SUM1       SUM2       MAX1
---------- ------------------------------ ---------- ---------- ----------
3 C                                       6          3          3
2 B                                       3          5          2
1 A                                       1          6          1

SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER(ORDER BY ID DESC) SUM1,
4  SUM(ID) OVER(ORDER BY ID) SUM2,
5  MAX(ID) OVER(ORDER BY ID DESC) MAX1
6  FROM T;

ID NAME                                 SUM1       SUM2       MAX1
---------- ------------------------------ ---------- ---------- ----------
1 A                                       6          1          3
2 B                                       5          3          3
3 C                                       3          6          3

SQL> SELECT ID,
2  NAME,
3  MAX(ID) OVER(ORDER BY ID) MAX1,
4  MIN(ID) OVER(ORDER BY ID) MIN1,
5  MAX(ID) OVER(ORDER BY ID DESC) MAX2,
6  SUM(ID) OVER(ORDER BY ID) SUM1
7  FROM T;

ID NAME                                 MAX1       MIN1       MAX2       SUM1
---------- ------------------------------ ---------- ---------- ---------- ----------
3 C                                       3          1          3          6
2 B                                       2          1          3          3
1 A                                       1          1          3          1

SQL> INSERT INTO T VALUES (4, 'E');

SQL> INSERT INTO T VALUES (5, 'D');

SQL> COMMIT;

SQL> SELECT * FROM T;

ID NAME
---------- ------------------------------
4 E
5 D
1 A
3 C
2 B

SQL> COL NAME FORMAT A5
SQL> SELECT ID,
2  NAME,
3  MAX(ID) OVER(ORDER BY ID) MAX1,
4  MAX(ID) OVER(ORDER BY ID DESC) MAX2,
5  MAX(ID) OVER(ORDER BY NAME) MAX3,
6  MAX(ID) OVER(ORDER BY NAME DESC) MAX4
7  FROM T;

ID NAME        MAX1       MAX2       MAX3       MAX4
---------- ----- ---------- ---------- ---------- ----------
4 E              4          5          5          4
5 D              5          5          5          5
3 C              3          5          3          5
2 B              2          5          2          5
1 A              1          5          1          5

Oracle总会选择最后出现的分析函数中的排序方法，作为整个结果集的排序方法。

• 博文量
1955
• 访问量
10525195