1.select sum(recordMaterialPrice * recordMaterialAmount * (case recordImportOrExport when true then 1 else -1 end)) from recordItem2.select recordMaterialId, sum(recordMaterialAmount*(case recordImportOrExport when true then 1 else -1 end))as kcl, sum(recordMaterialPrice * recordMaterialAmount) / sum(recordMaterialAmount) as pjjg from recordItem
解决方案 »
- 如何解决“对象名‘test’无效”的问题
- 从子查询中COUNT()多个条件记录的数量产生新纪录的问题
- 一个好象简单的select句子
- 求一sql语句
- 应该是系统问题
- 研究如何最快操作数据insert update delete
- Bcp命令导入的问题(无法打开主数据文件)?
- 急死人了,一个关于listview中图片显示的问题,解决后再给500分
- SQLServer里如何复制表
- 急用呀,在线等。请教一个SQL语句的写法,写对立刻把分全给他呀
- 在线求助:请问该触发器错在哪里?更新前后的num应该不变,但是现在某一行num变大了!
- 在server side的脚本里面 怎样可以让sqlserver2k的查询结果以XML的方式返回
from recordItem2、
select recordMaterialId,sum(case when recordImportOrExport=1 then recordMaterialAmount else -recordMaterialAmount end) as StoreMaterialAmount,sum(case when recordImportOrExport=1 then recordMaterialAmount*recordMaterialPrice else -recordMaterialAmount*recordMaterialPrice end)/sum(case when recordImportOrExport=1 then recordMaterialAmount else -recordMaterialAmount end) as StoreAvgPrice
from recordItem
group by recordMaterialId说多一句,只从材料出入库单据表来计算库存在用的时间长后速度会很慢,应该考虑设计问题。
以下是进出库均价,供参考:
select recordMaterialId,
sum(recordMaterialAmount*(case when recordImportOrExport=1 then 1 else -1) as 库存,
(sum(recordMaterialAmount*recordMaterialPrice*(case when recordImportOrExport=1 then 1 else -1))/(sum(recordMaterialAmount*(case when recordImportOrExport=1 then 1 else -1)) as 均价
from recordItem group by recordMaterialId
1.
SELECT 总金额 = SUM(CASE
WHEN recordImportOrExport = Export THEN recordMaterialAmount*recordMaterialPrice
WHEN recordImportOrExport = IMport THEN (-1)*recordMaterialAmount*recordMaterialPrice
END)
FROM recordItem2.
SELECT 库存量 = SUM(CASE
WHEN recordImportOrExport = Export THEN (-1)*recordMaterialAmount
WHEN recordImportOrExport = Import THEN recordMaterialAmount
END),
库存的平均价格 = SUM(CASE
WHEN recordImportOrExport = Import THEN recordMaterialPrice
ELSE 0
END)/SUM(CASE
WHEN recordImportOrExport = Import THEN 1
ELSE 0
END)
GROUP BY recordMaterialId
我其实还有一个表来存材料的信息比如库存,价格,但是很显然这是现在的数据,而项目需要能生成有某年某月的库存情况统计报表,我想出的方法只有通过单据计算,不知道你还有没有什么好办法?
这么做,牺牲的应该不单单是存储空间,因为单据可能修改!