如何在一个查询中从采购数量中减去销售数量

本文关键字：一个 查询 | 更新日期: 2023-09-27 18:31:24

我有两个查询： 

str = "select sum(Quantity) from AddStock where ProductId=" +id.ToString()  + " AND Type IN('Purchase','StockInward','Sale Return') AND EntryMode='Godown'";
Qty = dc.Retrieve_Datareader(str);
str = "select sum(Quantity) from AddStock where ProductId=" +id.ToString() + " AND Type IN('StockOutward','Sale','Wastage','Purchase Return') AND EntryMode='Godown'";
Qty1 = dc.Retrieve_Datareader(str);
return(Qty - Qty1);

如何在一个查询中编写上述两个查询并得到差异？

如何在一个查询中从采购数量中减去销售数量

您可以一次查询多个表。

查询：

SELECT SUM(as1.Quantity) AS SR,
       SUM(as2.Quantity) AS PR,
       SUM(as1.Quantity) - SUM(as2.Quantity) AS Difference
FROM   AddStock as1, AddStock as2
WHERE  as1.ProductId = @ProductId
AND    as2.ProductId = as1.ProductId
AND    as1.Type IN ('Purchase','StockInward','Sale Return')
AND    as2.Type IN ('StockOutward','Sale','Wastage','Purchase Return')
AND    as1.EntryMode = 'Godown'
AND    as2.EntryMode = as2.EntryMode

法典：

using(var command = new OleDbCommand(query, connection))
{
    command.Parameters.AddWithValue("@ProductId", id);
    using(var reader = command.ExecuteReader())
    {
        if(reader.Read())
        {
            var sr = Convert.ToInt32(reader[0]);
            var pr = Convert.ToInt32(reader[1]);
            var difference = Convert.ToInt32(reader[2]);
        }
    }
}
相关文章：