如何创建SQL的余额计算
问题描述:
我要创建SQL的余额计算的,我有4台这样的股票,stocklist,子,子表结果出来不work.Balance错误 看到我的代码如何创建SQL的余额计算
SELECT cc.mm,cc.dd,cc.name,cc.q1,cc.q2,cc.staff,(select sum(q1) - sum(q2)
from (SELECT s1.qty AS q1,"" AS q2,i.item_id,s2.receive_date as dd,concat('A',s2.id) AS mm
FROM mitem i
JOIN mstocklist s1 ON s1.item_id=i.item_id
JOIN mstock s2 ON s2.id=s1.stock_id
JOIN msupplier sp ON sp.supplier_id=s2.supplier_id
WHERE s2.receive_date BETWEEN '2016-10-01' AND '2017-09-30'
AND i.item_id='3' UNION SELECT "" AS q1,s1.qty AS q2,i.item_id,s2.receive_date as dd,concat('B',s2.id) AS mm
FROM mitem i
JOIN msublist s1 ON s1.item_id=i.item_id
JOIN msub s2 ON s2.id=s1.sub_id
WHERE s2.receive_date BETWEEN '2016-10-01' AND '2017-09-30'
AND i.item_id='3') as a where a.item_id=cc.item_id and a.mm <= cc.mm) as balance FROM(SELECT i.item_id,s2.receive_date as dd,i.name,s1.qty AS q1,"" AS q2,s2.staff_receive AS staff,concat('A',s2.id) AS mm FROM mitem i
JOIN mstocklist s1 ON s1.item_id=i.item_id
JOIN mstock s2 ON s2.id=s1.stock_id
JOIN msupplier sp ON sp.supplier_id=s2.supplier_id
WHERE s2.receive_date BETWEEN '2016-10-01' AND '2017-09-30'
AND i.item_id='3' UNION SELECT i.item_id,s2.receive_date as dd,i.name,"" AS q1,s1.qty AS q2,s2.staff_draw AS staff,concat('B',s2.id) AS mm FROM mitem i
JOIN msublist s1 ON s1.item_id=i.item_id
JOIN msub s2 ON s2.id=s1.sub_id
WHERE s2.receive_date BETWEEN '2016-10-01' AND '2017-09-30'
AND i.item_id='3') as cc ORDER BY dd ASC
谁可以帮助我愉悦。
答
如果我没有记错的话(你的代码是很难得到槽):有一个FROM
没有选择。 这一个:
FROM (SELECT i.item_id
, s2.receive_date AS dd
, i.name
, s1.qty AS q1
, "" AS q2
, (s1.qty*i.unitcost) AS cost
, s2.staff_receive AS staff
, s2.id AS mm
你行
你能提供http://sqlfiddle.com/表defintions和一些数据?此外,它将有助于获得确切的错误消息或显示您获得的内容以及您的期望。 – Myonara
它不会错误,但平衡无效的结果值。 –
所以请显示结果与预期结果。 – Myonara