sum over

按部门求薪酬总和

SELECT A.*, SUM(SAL) OVER(PARTITION BY DEPTNO) SUM_DEPT_SAL
  FROM SCOTT.EMP A
 ORDER BY DEPTNO, EMPNO;
​

结果：

改进：一步步求和

SELECT A.*, SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) SUM_DEPT_SAL
  FROM SCOTT.EMP A;
​

列出流水账每一笔钱支出后的余额

创建一个原始数据视图：

CREATE OR REPLACE VIEW v_t AS 
  SELECT 1000 AS ID,'预缴费用' AS NAME,30000 AS money FROM dual 
  UNION ALL 
  SELECT 7782 AS ID,'支出1' AS NAME,3450 AS money FROM dual 
  UNION ALL 
  SELECT 7839 AS ID,'支出2' AS NAME,6000 AS money FROM dual 
  UNION ALL 
  SELECT 7934 AS ID,'支出3' AS NAME,2300 AS money FROM dual;  
​

列出流水账每一笔钱支出后的余额

SELECT a.*,SUM(CASE  WHEN id=1000  THEN a.money ELSE -a.money END) 
  OVER(ORDER  BY ID) AS remaining FROM v_t a ORDER  BY ID;
​