Are now known to have a table below data
Report to increase a column balance, need according to the document number, time automatic sorting, then take the time before the sum of all the amount, if the same time the order number by the values,
At present is made, through the master query can be achieved, but at present a large amount of data card slowly die,
Do you have any good solution can improve the efficiency of??
The following is a build predicate sentence
- customer balance sheet
- drop table khyeb
The create table khyeb
(
DJH varchar (50), - order number
Khname varchar (50), - the customer name
Rq char (19), the date -
Je number (20, 8) - amount
)
Select * from khyeb for update
Prompt Importing table khyeb...
The set feedback off
The set define off
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A1 ', 'customers' A', '2020-09-23 09:00:00, 10.00000000);
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A2 ', 'A' customer, 'the 2020-09-23 10:00:00, 10.00000000);
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A3 ', 'customers' A', '2020-09-23 10:00:00, 10.00000000);
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A4 ', 'A' customers, 'the 2020-09-23 10:00:00, 10.00000000);
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A5 ', 'A' customers, 'the 2020-09-23 11:00:00, 10.00000000);
Insert into khyeb (DJH, KHNAME, RQ, JE)
Values (' order number A6 ', 'A' customers, 'the 2020-09-23 12:00:00, 10.00000000);
Prompt the Done.
Below is through the realization of the master query, but at present there are too many data, a direct result of true is too slow,,, to seek optimization, I think time points mainly concentrated in the master query,
B. select *,
(select NVL (sum (je), 0) as ye from khyeb eb where 1=1 and eb. DjhAs the balance of the from khyeb b
The following is a SQL query
CodePudding user response:
Help you a great god,