库存管理

Question

我有两张桌子，INWARDS和OUTWARDS。我必须在每个日期向内显示总库存和向外显示每个不同股票的总数，以及该日剩余的股票余额。我不需要按任何日期分组，我只想要库存的总数量。

我将我的查询写成如下：

SELECT
    I.Date as'Date',
    P.ResourceName as 'Item Name',
    P.ResourceRate as'Rate',
    i.Qty as Inwards Quantity',
    (   select qty
        from Outwards_master
        where I.Date=S.Date and I.Nameofitem =S.Nameofitem
    ) as 'Outwards Quantity',
    (   select (sum(i.qty)-sum(S.Qty))
        from Inwards_Master
            inner join Outwards_Master
            on i.Nameofitem=S.Nameofitem
    ) as 'Balance'
From Resource_Master P, Inwards_Master I, Outwards_Master S
WHERE (I.Nameofitem= P.ResourceName) and P.TenderCode = 1

这个查询给了我一个错误。有什么问题吗？

错误是：

Msg 8120、级别16、状态1、第1列'Inwards_Master.Date‘在select列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句中。

Answer 1

根据您的查询，我猜测您想要这样的东西：

SELECT I.Date as Date, 
  P.ResourceName as ItemName,
  P.ResourceRate as Rate,
  i.Qty as InwardsQuantity,
  s.qty as OutwardsQuantity,
  i.qty-S.Qty as Balance,
  O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
  ON O.UnitCode = P.Unitcode
INNER JOIN Inwards_Master I
  ON I.Nameofitem= P.ResourceName
INNER JOIN Outwards_Master S 
  ON I.Date=S.Date 
  AND I.Nameofitem =S.Nameofitem
WHERE P.TenderCode=1

如果要显示所有项，则可能要使用LEFT JOIN

SELECT I.Date as Date, 
  P.ResourceName as ItemName,
  P.ResourceRate as Rate,
  i.Qty as InwardsQuantity,
  s.qty as OutwardsQuantity,
  i.qty-S.Qty as Balance,
  O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
  ON O.UnitCode = P.Unitcode
LEFT JOIN Inwards_Master I
  ON I.Nameofitem= P.ResourceName
LEFT JOIN Outwards_Master S 
  ON I.Date=S.Date 
  AND I.Nameofitem =S.Nameofitem
WHERE P.TenderCode=1

编辑#1，根据您的评论，我不太清楚问题是什么，但是您可以尝试在子查询中获取总数量：

SELECT I.Date as Date, 
  P.ResourceName as ItemName,
  P.ResourceRate as Rate,
  i.TotalInQty as InwardsQuantity,
  s.TotalOutQty as OutwardsQuantity,
  i.TotalInQty-S.TotalOutQty as Balance,
  O.UnitSymbol As ItemUnit
FROM UnitMaster O
INNER JOIN Resource_Master P
  ON O.UnitCode = P.Unitcode
LEFT JOIN
(
  select sum(i.Qty) TotalInQty, Nameofitem
  from Inwards_Master I
  group by Nameofitem
) I
  ON I.Nameofitem= P.ResourceName
LEFT JOIN
(
  select sum(s.qty) TotalOutQty, Nameofitem
  from Outwards_Master S 
  group by s.Nameofitem
) S
  ON I.Date=S.Date 
  AND I.Nameofitem =S.Nameofitem
WHERE P.TenderCode=1