Help please understand the creation of views ...

There is a sign with numbers for each day:

+------------+-------+ | date | value | +------------+-------+ | 2018-02-10 | 5 | | 2018-02-11 | 2 | | 2018-02-12 | 3 | | 2018-02-13 | 1 | | 2018-02-14 | 7 | +------------+-------+

There is another label initial value: 100.

We need a view that will count the remainder.

Example. 2018-02-09 there were 100pcs of goods. every day is written off.

The presentation should show a balance for each day.

As a result, get:

 +------------+-------+ | date | rest | +------------+-------+ | 2018-02-10 | 95 | | 2018-02-11 | 93 | | 2018-02-12 | 90 | | 2018-02-13 | 89 | | 2018-02-14 | 82 | +------------+-------+

As I understand it, for the first line you need to make the difference of the "Initial quantity" field and the first day, and for the rest, make the difference of what happened in the presentation in the previous line and the loss per day.

Here is the beginning of the query that we managed to write:

 select `ks`.`Date` AS `Date`,if((`ks`.`Id` = '1'),(`hc`.`HС` - `ks`.`HC`),'0') AS `HC` from ((`CS`.`KH` `ks` join `CS`.`Users` `hc`) where (`hc`.`TableName` = 'K')

    1 answer 1

    With the request - like this:

     SELECT t.`date`, t.value, @amount:=@amount-t.value rest FROM `table` t, (SELECT @amount:=100) dummy ORDER BY t.`date` ASC;

    With the presentation - like this:

     CREATE VIEW GetReminder AS SELECT t1.`date`, t1.value, 100-SUM(t2.value) rest FROM `table` t1, `table` t2 WHERE t1.`date` >= t2.`date` GROUP BY t1.`date`; SELECT * FROM GetReminder;
    • For some reason, the request: SET (the dog is not inserted into the comment) amount: = 100; displays an error in the view - # 1064 - You have an error in your SQL syntax; If you make a simple query, then it is executed ... A view definition is subject The following statements cannot contain a subquery in the FROM clause. The SELECT statement cannot refer to system or user variables. This is in the documentation ( - Razangann
    • Ok, corrected. Checked works. - Akina
    • Thank you very much! Earned :) Do you think this view will load the base if there are 600 days? :) - Razangann
    • I do not know, it is necessary to test. Although from my point of view, 360 thousand records in the union - not so much. Another thing is that I do not understand why I needed a presentation - for the task it is completely meaningless. - Akina
    • Simply, the loss data can change, but in the end you need a plate of the remainder of the day. If you just keep the balance, which is considered every day, then when editing a loss on some last day, you will have to recalculate the balance from this date on, thought the submission machine would do it easier than need to call the conversion procedure for example :) - Razangann