现在有以下几个SQL,请问我想在她们之中做加减操作,将结果通过一个Select输出,应该如何写语句呢,谢谢!
数量1:
SELECT Sum(t2.FQty) as 数量1 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = 21 Group by t2.FItemID,t1.FTrantype数量2:
SELECT Sum(t2.FQty) as 数量2 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = '41' and t2.FSCStockID='1562' Group by t2.FItemID,t1.FTrantype数量3:
SELECT Sum(t2.FQty) as 数量3 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = '41' and t2.FSCStockID='159' Group by t2.FItemID,t1.FTrantype
希望能进行的操作是:
Select 物料名称,(数量1 + 数量2 - 数量3),日期 from XXXX where XXX Group by XXX,请问应该如何操作呢!!谢谢!
数量1:
SELECT Sum(t2.FQty) as 数量1 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = 21 Group by t2.FItemID,t1.FTrantype数量2:
SELECT Sum(t2.FQty) as 数量2 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = '41' and t2.FSCStockID='1562' Group by t2.FItemID,t1.FTrantype数量3:
SELECT Sum(t2.FQty) as 数量3 FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
WHERE t1.FTranType = '41' and t2.FSCStockID='159' Group by t2.FItemID,t1.FTrantype
希望能进行的操作是:
Select 物料名称,(数量1 + 数量2 - 数量3),日期 from XXXX where XXX Group by XXX,请问应该如何操作呢!!谢谢!
,t1.FTrantype
Sum(case when t1.FTranType = 21 then t2.FQty else 0 end)
+SUM(case when t1.FTranType = '41' and t2.FSCStockID='1562' then t2.FQty else 0 end)
-SUM(case when t1.FTranType = '41' and t2.FSCStockID='159' then t2.FQty else 0 end)
FROM ICStockBill t1 INNER JOIN ICStockBillEntry t2 ON t1.FInterID = t2.FInterID
Group by t2.FItemID,t1.FTrantype
你的group by后加日期了吧!如果加的话对日期处理下,select里的日期也处理下!
弄成一月,一天或者一小时之类的!不要直接group by。