Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

sql - oracle query sequential summation per rows

I would like to ask to make a query like the following table?

-------------------------------------
ID   Date        Input  Output  Total
-------------------------------------
1   2016-05-01  10       0      10
1   2016-05-02  2       -1      11
1   2016-05-03  0       -5      6
1   2016-05-04  0       -1      5 

assume I have a table "table_a" that has a column (ID, name, date, input, output). how to create query with results as above.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

You can use SUM OVER function:

SELECT *,
    SUM(Input + Output) OVER(PARTITION BY ID ORDER BY Date) AS Total
FROM table_a

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...