其中sum为最新值,id为物资id
teamid为物资id,sum为操作数量 ,state0转入1转出
其中
sql
select name,type,nameteam,metering,time,sum,housum,if(state=1,housum+shujv.sum,housum-shujv.sum) qiansum from
(
SELECT jilu.id jiluid,wuzi.id wuziid,wuzi.name,wuzi.type,wuzi.metering,gs.nameteam,jilu.time,jilu.sum,state,
(
wuzi.sum
+
ifnull((
SELECT sum(`sum`) FROM fangxun.app_team_record where teamid=wuzi.id and id>jilu.id and state=1
),0)
-
ifnull((
SELECT sum(`sum`) FROM fangxun.app_team_record where teamid=wuzi.id and id>jilu.id and state=0
),0)
) housum
FROM fangxun.app_team_record jilu
left join app_team wuzi on jilu.teamid=wuzi.id
left join app_groupcompany gs on jilu.companyid=gs.id
) shujv
sql也很好理解 ,但记录一下 查询表时可以用这整条sql中其他表的字段进行判断(碰巧试出来的) 如上 ** where teamid=wuzi.id and id>jilu.id **
但sql太长了 效率也不高 于是导出查询的数据 往记录表加俩字段(操作前,操作后)再导入 改下操作数据的接口 直接查了