列1 列2 列3
1 0 0 500
2 20 10 500+20-10=510
3 30 20 510+30-20=500
------------------------------------------------------
从第2行起计算列三的值?SQL怎么写?
1 0 0 500
2 20 10 500+20-10=510
3 30 20 510+30-20=500
------------------------------------------------------
从第2行起计算列三的值?SQL怎么写?
SELECT a,
b,
c,
(b-c) + lag(b-c) over(order by a) as e
FROM
( SELECT 1 AS a, 0 AS b, 0 AS c FROM dual
UNION ALL
SELECT 2, 20, 10 FROM dual
UNION ALL
SELECT 3, 30, 20 FROM dual
)
)
--结果
A B C E
---------------------- ---------------------- ---------------------- ----------------------
1 0 0 500
2 20 10 510
3 30 20 520
select 1 a ,0 b ,0 c from dual union all
select 2 ,20 ,10 from dual union all
select 3 ,30 ,20 from dual)select tb.* ,500+sum(b-c)over(order by a)from tb
我也在想是用LAG还是用SUM比较合适。如果是加上一行的值用LAG,如果是所有行的累计用SUM。
-----------------------------
1 0 0 a
2 b c a+b-c
3 e f a+b-c+e-f
: : :
: : :
最后一行
: : : a+sum(A)-sum(B)a,b,c,b是通过视图查询出来的值
A 是增加的值
B 是减少的值
C 是余额
这个是余额明细账的查询;
--有多少字段就加多少字段
select 序号,A,B,C,sum(A-B+C)over(order by 序号) from table
ename,
job,
sal
FROM emp
ORDER BY sal; EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7521 WARD SALESMAN 1250
7654 MARTIN SALESMAN 1250
7934 MILLER CLERK 1300
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7839 KING PRESIDENT 5000SQL>
LAGThe LAG function is used to access data from a previous row. The following query returns the salary from the previous row to calculate the difference between the salary of the current row and that of the previous row. Notice that the ORDER BY of the LAG function is used to order the data by salary.SELECT empno,
ename,
job,
sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM emp; EMPNO ENAME JOB SAL SAL_PREV SAL_DIFF
---------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 800 0 800
7900 JAMES CLERK 950 800 150
7876 ADAMS CLERK 1100 950 150
7521 WARD SALESMAN 1250 1100 150
7654 MARTIN SALESMAN 1250 1250 0
7934 MILLER CLERK 1300 1250 50
7844 TURNER SALESMAN 1500 1300 200
7499 ALLEN SALESMAN 1600 1500 100
7782 CLARK MANAGER 2450 1600 850
7698 BLAKE MANAGER 2850 2450 400
7566 JONES MANAGER 2975 2850 125
7788 SCOTT ANALYST 3000 2975 25
7902 FORD ANALYST 3000 3000 0
7839 KING PRESIDENT 5000 3000 2000
(sum(A-B)
over(order by 序号
rows between unbounded preceding and current row))+a C
from tabl