Совокупный запрос на запасы без участия в начальной таблице запасов ⇐ MySql

[Anonymous]

Я разрабатываю программу управления акциями, здесь у меня есть входящая таблица , как ниже:

• id ( integer )
• date ( )
• ( ) ( varchar )
• warehouse_id ( tinyint )
• goods_id ( Integer )
• decimal
• )
• ) />price (decimal)
• amount (decimal)

id
date
type
Warehouse_ID
Goods_id
Количество
200 < /td>
¥ 1.50 < /td>
¥ 300 < /td>
< /tr>

2 < /td>
25-1 < /td>
1 < /td>
25-8-3 < /td>
unsume < /td>
60 < /td>

60 < /td>

25-6 < /td>
25-8-7
consume
90



And I can easily design a query Чтобы объединить входящие и внешние таблицы, как показано ниже: < /p>
WITH cte AS (
SELECT id, incoming_id, date, type, quantity, SUM(quantity) OVER w AS cum_qty
FROM outcoming
WINDOW w AS (PARTITION BY incoming_id ORDER BY date, id)
)
SELECT A.id, A.date AS in_date, A.type AS in_type, A.warehouse_id AS wh_id, A.goods_id AS g_id, A.quantity AS in_qty, B.date AS out_date, B.type AS out_type, B.quantity AS out_qty, B.cum_qty, A.quantity-IFNULL(B.cum_qty,0) AS left_qty
FROM incoming AS A
LEFT JOIN cte AS B ON A.id=B.incoming_id
ORDER BY A.date, A.id, B.date, B.id
< /code>



id < /strong> < /th>
in_date < /ntra> < /> < /> < /> < / /> < /> < /> < /> < /> < /> < /> < /> < /> 1 < /td>
200 < /td>
25-8-2 < /td>
50 < /td>
50 < /td>
150 < /td>
< /tr>

1
90 < /td>
< /tr>

1 < /td>
25-8-1 < /td>

1 < /td>
1 < /td>
200 < /td>
25-4-4 < /td>
25-4-4 < /td>

Польсовать < /td>
30 < /td>
140 < /td>
60 < /td>
< /tr>


2
25-8-5
purachse
1
1
70 < /td>
230 < /td>
< /tr>

2 < /td>
purachse < /td>
1 < /td>
1 < /td>
300 < /td>
cum_in_qty
cum_out_qty
left_qty




1 < /td>
1 < /td>
25-8-1 < /td>
200 < /td>
< /tr>

1 < /td>
1 < /td>

Употреблять < /td>
< /td>
50 < /td>
200 < /td>
150 < /td>
< /tr>

1 < /td>
1 < /td>
200 < /td>
110 < /td>
90 < /td>
< /tr>

1 < /td>
1 < /td>
25-8-4
Услугивать

30
30

< /td>

500 < /td>
140 < /td>
360 < /td>
< /tr>

1 < /td>
1
25-8-6
unsume

500 < /td>
210 < /td>
290 < /td>
< /tr>



Подробнее здесь: https://stackoverflow.com/questions/797 ... tock-table