获取在商店

Question

我想得到这样一个表其余项目：

itemid | remaining_quantity 
-------|--------- 
1  | 100 
-------|--------- 
3  | 150 
-------|--------- 
4  | 300 

显着表：

• Purshase（ID，为itemid，数量，isReceived）//由我们收取？

• 销售（id，itemid，数量，isReceived）//是否被客户接收？

• 项目（ID，init_quantity）

• LostItem（ID，商品ID，数量）//这是用来解决实际数量，如果它实际上是增加或减少

，你可以看，我们应该只考虑那些销售和追求isReceived = True。在LostItem中，数量可能为负数，表示实际数量少于计算数量。它也可以是积极的，意味着实际的计算更多。

（即我想Purshase - 售+ LostItem +项目）

这里是我迄今所做的：（在没有行结果）

select i.itemid, 
     isnull(sum(p.quantity), 0) - isnull(sum(s.quantity), 0) 
     + isnull(sum(t.quantity), 0) + isnull(sum(i.itemInitQuantity), 0) 
    as remaining_quantity 
from [Purshase] p, [Sale] s, [LostItem] t, [Item] i 
where p.pitem = i.itemid and s.saleitem = i.itemid and t.item = i.itemid 
     and p.isReceived = 1 and s.isReceived = 1 
group by i.itemid 

Answer 1

找到它！ （Alhamdu lellah）

SELECT itemid, init, sales_all, sales_mostlma, purchases_all, 
purchases_mstlma, taswiyah, sales_mardood, purchases_mardood, 
sales_request, pruchases_request,init - sales_mostlma + purchases_mstlma + 
taswiyah + sales_mardood - purchases_mardood AS stock, init - sales_all + 
purchases_all + taswiyah + sales_mardood - purchases_mardood AS available, 
init - sales_all + purchases_all + taswiyah + sales_mardood - 
purchases_mardood - sales_request + pruchases_request 
AS available_with_request 
FROM (SELECT itemid,itemInitQuantity AS init, 
(SELECT ISNULL(SUM(salequantity), 0) AS cnt FROM dbo.Sale AS s 
WHERE (saleitem = i.itemid) AND (tasleemTime IS NOT NULL)) AS sales_mostlma, 
(SELECT ISNULL(SUM(salequantity), 0) AS cnt FROM dbo.Sale AS s 
WHERE (saleitem = i.itemid)) AS sales_all, 
(SELECT ISNULL(SUM(pcquantity), 0) AS cnt FROM dbo.Purshase AS p 
WHERE (pitem = i.itemid) AND (tasleemTime IS NOT NULL)) AS purchases_mstlma, 
(SELECT ISNULL(SUM(pcquantity), 0) AS cnt FROM dbo.Purshase AS p 
WHERE (pitem = i.itemid)) AS purchases_all, 
(SELECT ISNULL(SUM(quantity), 0) AS cnt FROM dbo.TaswiyahGardiyah AS t 
WHERE (item = i.itemid)) AS taswiyah, 
(SELECT ISNULL(SUM(sm.smquantity), 0) AS Expr1 FROM dbo.SaleMardood AS sm 
INNER JOIN dbo.Sale AS ss ON sm.smsale = ss.saleid 
WHERE (ss.saleitem = i.itemid)) AS sales_mardood, 
(SELECT  ISNULL(SUM(pm.purmquantity), 0) AS Expr1 
FROM dbo.PurshaseMardood AS pm 
INNER JOIN dbo.Purshase AS pur ON pm.purmpurshase = pur.pid 
WHERE (pur.pitem = i.itemid)) AS purchases_mardood, 
(SELECT ISNULL(SUM(prquantity), 0) AS Expr1 FROM dbo.PurshaseRequest AS pr 
WHERE (pritem = i.itemid)) AS pruchases_request, 
(SELECT ISNULL(SUM(scquantity), 0) AS Expr1 FROM dbo.SaleRequest AS sr 
WHERE (scitem = i.itemid)) AS sales_request 
FROM dbo.Item AS i) AS myTable