Question

SQL如何编写语句，实现查询出来的数据可以相加减，急急急！！

以下为通过日期设置日期查询出来的数据，我想实现：期初数据+总入库数量-总出库数量，该如何编写
SELECT t3.FNumber AS '物料代码',
t3.FName AS '物料名称',
t3.FModel AS '规格型号',
t4.FName AS '单位',
t5.FBegBal as '期初数据',
SUM(CASE WHEN t1.FTranType=1 THEN t2.FQty ELSE 0 END) AS '外购入库数量',
SUM(CASE WHEN t1.FTranType=2 THEN t2.FQty ELSE 0 END) AS '产品入库数量',
SUM(CASE WHEN t1.FTranType=5 THEN t2.FQty ELSE 0 END) AS '委外入库数量',
SUM(CASE WHEN t1.FTranType=10 THEN t2.FQty ELSE 0 END) AS '其他入库数量',
SUM(CASE WHEN t1.FTranType=40 THEN t2.FQty ELSE 0 END) AS '盘盈入库数量',
SUM(CASE WHEN t1.FTranType in (1,2,5,10,40) THEN t2.FQty ELSE 0 END) AS '总入库数量',
SUM(CASE WHEN t1.FTranType=21 THEN t2.FQty ELSE 0 END) AS '销售出库数量',
SUM(CASE WHEN t1.FTranType=24 THEN t2.FQty ELSE 0 END) AS '生产领料数量',
SUM(CASE WHEN t1.FTranType=28 THEN t2.FQty ELSE 0 END) AS '委外出库数量',
SUM(CASE WHEN t1.FTranType=29 THEN t2.FQty ELSE 0 END) AS '其他出库数量',
SUM(CASE WHEN t1.FTranType=43 THEN t2.FQty ELSE 0 END) AS '盘亏出库数量',
SUM(CASE WHEN t1.FTranType in (24,28,29,21,43) THEN t2.FQty ELSE 0 END) AS '总发出数量',
--'期初数据'+'总入库数量'-'总发出数量' as '结存数量',

t3.FQtyDecimal AS '数量精度',
t3.FPriceDecimal AS '单价精度'
FROM ICStockBill t1,ICStockBillEntry t2,t_ICItem t3,t_MeasureUnit t4,ICInvBal t5
WHERE t1.FInterID=t2.FInterID
AND t2.FItemID=t3.FItemID
AND t4.FItemID=t3.FUnitID
and t1.FCancellation=0
and t2.FItemID=t5.FItemID
AND t1.FDate>='********'
and t1.FDate<='########'
AND t4.FStandard=1
AND t3.FNumber>='*ItemNo*'
AND t3.FNumber<='#ItemNo#'
and t5.FPeriod=9
Group By t3.FItemID,t3.FNumber,t3.FName,t3.FModel,t4.FName,t3.FQtyDecimal,t3.FPriceDecimal,t5.FBegBal

Answer 1

select (期初数据+总入库数量-总出库数量) as 库存 from （你题目中的查询语句）t0;

追问

最后那个 t0是什么意思的

追答

t0是任意取的一个别名，将你题目中的查询语句的结果命名为t0.

Answer 2

嵌套一下就行了，先查询出基础数据，然后在此基础上计算