SUM上的SQL JOIN计算问题

[塞德梅萨马利]

大家好，
我的数据库中有三个表格，分别是Stockin，Stockout和Products。下面是它们的详细架构。

CREATE TABLE [dbo].[Products](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](150) NOT NULL,
    [Re_Order] [float] NOT NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[StockinTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [Sup_ID] [varchar](10) NOT NULL,
    [Sup_Name] [varchar](50) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](100) NOT NULL,
    [Expiry] [date] NOT NULL,
    [Units] [float] NOT NULL,
    [Cost] [float] NOT NULL,
PRIMARY KEY CLUSTERED

和缺货如下：

CREATE TABLE [dbo].[StockoutTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [Invoice] [varchar](12) NOT NULL,
    [Cust_Name] [varchar](150) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](150) NOT NULL,
    [Boxes] [float] NOT NULL,
    [Pcs] [float] NOT NULL,
    [Price] [float] NOT NULL,
PRIMARY KEY CLUSTERED

现在的问题是，当我尝试使用以下SQL查询时，一切都很好（就计算而言）：

SELECT pr.Prod_ID, pr.Prod_Name, sum(sin.Units) as [Units] FROM 产品展示 as pr
JOIN 库存表 AS sin ON (pr.Prod_Name = sin.Prod_Name)
WHERE pr.Prod_Name = 'CIPOLE FRESCO 5KG'

But when I update my Query to include the 缺货表 in JOIN then the calculation is terribly wrong as the sum is calculated for each time there is an occurrence of an item in stockout 表。 Any solution or fixes? Below is erroneous calculation (or JOIN).

SELECT pr.Prod_ID, pr.Prod_Name, sum(sin.Units) as [Units] FROM 产品展示 as pr
JOIN 库存表 AS sin ON (pr.Prod_Name = sin.Prod_Name)
JOIN 缺货表 AS sout ON (pr.Prod_Name = sout.Prod_Name)
WHERE pr.Prod_Name = 'CIPOLE FRESCO 5KG'
GROUP BY pr.Prod_ID, pr.Prod_Name

例如，第一个SQL语句以SUM返回6025的值是正确的，但是第二个语句将该值跳转到72300，这是非常不正确的。

 

[金西尼]

首先，在我看来，这两个 存货和脱销 表不应该有那些 产品ID和产品名称 列，但是应该有一个外键 ID 中的列 产品展示 表。也就是说，除非这些表引用的库存中没有记录， 产品展示 表。

 

[塞德梅萨马利]

感谢您的回复。如果您能指导我如何轻松地在当前架构中进行更改，我将不胜感激，因为我真的想尽快完成更改。任何帮助表示赞赏。

 

[金西尼]

您为什么要包括 缺货表 首先？您没有在查询中的其他任何地方使用它，所以为什么根本没有它呢？第一个查询会执行您想要的操作，那么为什么不只使用第一个查询呢？

实际上，鉴于您当前的架构，您为什么还要使用 产品展示？您正在过滤 产品名称 但是你有那一列 库存表 因此，该表就是您所需要的：

SELECT 产品ID, 产品名称, SUM(Units) AS [Units]
FROM 库存表
WHERE 产品名称 = 'CIPOLE FRESCO 5KG'

您正在加入的事实 产品展示 根本就是你不应该拥有的证据 产品名称 列中 库存表和the fact that you are joining against other than the primary key is evidence that your schema is just plain bad.

 

[塞德梅萨马利]

我的目标是"Purchase Cost" from "Stockin table" 和 "销售价格和发票编号" from the "Stockout table"。这些表完全不同，除了产品ID和名称列。是的，我犯了以下错误：未正确定义架构以及未加强"FOREIGN KEY"约束。目前正在努力解决问题。

 

[金西尼]

您为什么认为通过单个查询应该可以做到这一点？如果您需要有关库存的信息，请从 库存表和if you want information about stock out then get it from the 缺货表。如果那需要两个查询，则使用两个查询。

由于没有这种关系，因此无法在一个表中的记录与另一个表中的记录之间创建1：1对应关系。您必须拥有有关单个商品的信息，而不仅仅是商品的信息，例如如果每个项目都有序列号。联接总是会增加行数。如果固有关系为1：1，则乘积为1。如果该关系为多对多，就像两个表一样，那么您将乘以许多，得到更多。如果您对每种组合都不感兴趣，那么联接就不是您想要的。

 

[塞德梅萨马利]

好的，这是交易。我想得到"Current stock"库存和缺货产生的信息。先购买一些数量（库存），然后再出售一些数量（针对每个项目）（缺货）。要得到"当前库存状况"我需要对库存数量求和，然后减去库存数量？我认为（也许是错误的）是，使用联接和总和将基于这两个表中的数据提供当前的股票头寸吗？
连接的另一个目的是在一个输出表中显示所有数据的汇总（库存和库存）。不可能吗？

 

[金西尼]

To get "当前库存状况"我需要对库存数量求和，然后减去库存数量？

是的，没错。阅读您写的内容。您先进行求和，然后进行减法。这意味着需要两个查询来获得两个和，然后减去。如果需要，可以在SQL中进行减法运算，但仍然必须先执行两个子查询。您在末尾执行一次减法，而不是在每对记录上都减法一次。

连接的另一个目的是在一个输出表中显示所有数据的汇总（库存和库存）。不可能吗？

可能是可能的，但您可能想要实际定义您的意思"summary"而不是期望我们承担。无论是什么，几乎肯定都不会通过加入来完成。最有可能的候选者似乎是两个查询结果的并集，然后按 日期.

 

[塞德梅萨马利]

是的，就是这样。阅读您写的内容。您先进行求和，然后进行小费。这意味着需要两个查询来获得两个和，然后减去。如果需要，可以在SQL中进行减法运算，但仍然必须先执行两个子查询。您在末尾执行一次减法，而不是在每对记录上都减法一次。

好的，非常感谢这个主意。要检查一下。

可能是可能的，但您可能想要实际定义您的意思"summary"而不是期望我们承担。无论是什么，几乎肯定都不会通过加入来完成。最有可能的候选者似乎是两个查询结果的并集，然后按 日期.

摘要将是"产品编号，产品名称，日期，有效期，库存单位，缺货单位，重新订购，当前库存"。感谢您的宝贵意见。要付诸行动。

 

[塞德梅萨马利]

经过大量的研究和在线搜索之后，我很高兴找到解决方案。感谢JMC为我提供指导，因为我必须做"SUB-QUERY"提取结果。最终（正确）查询如下。

SELECT sin.Prod_ID, sin.Prod_Name, sin.Bought, ISNULL(sout.SOLD,0) as [Sold],
ISNULL((sin.Bought - sout.SOLD),0) AS [Stock Now]
FROM (SELECT 产品ID, 产品名称, ISNULL(SUM(Units),0) AS Bought
    FROM 库存表 sout GROUP BY 产品ID, 产品名称) sin
        LEFT JOIN  (SELECT 产品ID, ISNULL(SUM(Boxes),0) AS SOLD
        FROM 缺货表 GROUP BY 产品ID) sout
     ON sin.Prod_ID = sout.Prod_ID
WHERE sin.Prod_Name = 'CIPOLE FRESCO 5KG'