17,377
社区成员
发帖
与我相关
我的任务
分享
select b.名称,sum(a.成本金额) as 总入库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = 1
group by b.名称
union all
select b.名称,sum(a.成本金额) as 总出库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = -1
group by b.名称
select x.总入库金额-y.总出库金额 from
(select b.名称,sum(a.成本金额) as 总入库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = 1
group by b.名称) x,
(select b.名称,sum(a.成本金额) as 总出库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = -1
group by b.名称) y;
select a.总入库金额-b.总出库金额 from
(select b.名称,sum(a.成本金额) as 总入库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = 1
group by b.名称) a,
(select b.名称,sum(a.成本金额) as 总出库金额
from 药品收发记录 a,部门表 b
where a.库房id=b.id and b.名称 ='西药库' and a.入出系数 = -1
group by b.名称) b;
with a as
(select 'a' cola, 20 money,'1' flag from dual
union all
select 'a' ,30 money,'-1' from dual
union all
select 'a' ,36 money ,'1' from dual
union all
select 'a' ,43 money,'1' from dual
union all
select 'a' ,23 money,'-1' from dual
union all
select 'a' ,97 money,'1' from dual
union all
select 'a' ,65 money,'-1' from dual)
select cola ,sum(decode(flag,'1',money,0)) 入库值, sum(decode(flag,'-1',money,0)) 出库值,
sum(decode(flag,1,money,0)) - sum(decode(flag,'-1',money,0)) 库存值
from a where a.cola = 'a' group by cola