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

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

SQL> INSERT INTO T VALUES (1, 'A');

SQL> INSERT INTO T VALUES (3, 'C');

SQL> INSERT INTO T VALUES (2, 'B');

SQL> COMMIT;

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  FROM T;

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

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

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

SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER() SUM1
4  FROM T;

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

SQL> SET AUTOT ON STAT
SQL> SELECT ID,
2  NAME,
3  SUM(ID) OVER(ORDER BY ID) SUM1
4  FROM T
5  ORDER BY ID;

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

----------------------------------------------------------
4  recursive calls
0  db block gets
15  consistent gets
0  redo size
572  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
1  sorts (memory)
0  sorts (disk)
3  rows processed

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

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

----------------------------------------------------------
4  recursive calls
0  db block gets
15  consistent gets
0  redo size
572  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
2  sorts (memory)
0  sorts (disk)
3  rows processed

• 博文量
1954
• 访问量
10764779