MS Access 2010查询多次拉出相同的记录,sql挑战

背景：
我的公司库存钢筋.我们提议将条形切成碎片.从库存方面来说,我们希望跟踪每个酒吧的长度,从进入仓库的那一刻起,到仓库中的时间(可能会被切成小块),直到整个酒吧出售并消失.

数据库：
提出问题的查询是咨询3个表格;

> Barstock(以下字段)

> BatchNumber(收到的所有酒吧,相同的生产热量)
> BarNo(个人酒吧)
> Orginial Length(在库存中收到的条的长度

(BatchNumber和BarNo组合,是主键)

>销售

> ID(主键)
> BatchNumber
> BarNo
>已售出数量

>预订(卖方可以保留一些材料,当客户表示兴趣,但需要时间来决定)

> ID(主键)
> BatchNumber
> BarNo
>数量保留

我想将这三个表中的信息拉到一个列表中,显示：
-Barstock.orginial length收到
– Sales.Quantity已售出售出
– 收到 – 按库存出售
– reservation.Quantity保留为预留
– 现货 – 保留为可用.

问题是我吮吸sql.我尽最大努力研究了联盟和内心的联系,但我的努力一直都是徒劳的.我通常依靠设计视图来生成我需要的Sql语句.有了设计视图,我想出了以下Sql：

SELECT 
  BarStock.BatchNo,BarStock.BarNo,First(BarStock.OrgLength) AS Recieved,Sum(Sales.QtySold) AS SumAvQtySold,[Recieved]-[SumAvQtySold] AS [On Stock],Sum(Reservation.QtyReserved) AS Reserved,([On Stock]-[Reserved])*[Skjemaer]![Inventory]![unitvalg] AS Available
FROM 
  (BarStock 
    INNER JOIN Reservation ON (BarStock.BarNo = Reservation.BarNo) AND (BarStock.BatchNo = Reservation.BatchNo)
  ) 
    INNER JOIN Sales ON (BarStock.BarNo = Sales.BarNo) AND (BarStock.BatchNo = Sales.BatchNo)
GROUP BY 
  BarStock.BatchNo,BarStock.BarNo

我知道查询多次拉同一条记录,因为;
– 当我删除GROUP BY术语时,我会得到几条完全相同的记录.
– 但是,相应表中只有这些记录的一个实例.

我希望我已经能够正确解释自己,请问我是否需要详细说明.

感谢您抽出宝贵时间来看看我的问题！

解决方法

从您的数据库架构,似乎：

>给定的BatchNumber / BarNo可能有多个销售记录(例如,我可以想象多个客户可能已经购买了相同栏的子部分).
>给定的BatchNumber / BarNo可能有多个Reservation记录(例如,同一个bar的多个部分可能是’reserved’)

要检查这些表中是否确实有多条记录,请尝试以下操作：

SELECT CountOfDuplicates
FROM   (SELECT COUNT(*) AS CountOfDuplicates
        FROM   Sales
        GROUP  BY BatchNumber & "," & BarNo)
WHERE  CountOfDuplicates > 1

如果查询返回一些记录,那么有重复项,这可能是您的查询返回错误值的原因.

从头开始

现在,让你的查询工作的诀窍是真正考虑你想要显示的主要数据是什么,并从那开始：

>您基本上想要股票中所有柱的列表.
这些酒吧中的一些可能已被出售,或者可能被保留,但如果不是,则应显示库存中的数量.您当前的查询永远不会向您显示.
>对于库存中的每个库,您需要列出已售出的数量和预留的数量,并将它们组合在一起以查找剩余可用数量.

所以很清楚,你的中心数据是库存中的柱状列表.

不要试图立即将所有内容都放到一个大型查询中,而是最好为每个目标创建简单查询,并确保在每种情况下都能获得正确的数据.

只是酒吧

根据您的解释,每个单独的栏都记录在BarStock表中.
正如我在评论中所说,根据我的理解,所有交付的酒吧在BarStock表中都有一条记录,没有重复.因此,您应该测量库存的主要清单是BarStock表：

SELECT BatchNumber,BarNo,OrgLength
FROM   BarStock

只是销售

同样,这应该非常简单：我们只需要找出每个BatchNumber / BarNo对的总销售量：

SELECT BatchNumber,Sum(QtySold) AS SumAvQtySold
FROM   Sales
GROUP BY BatchNumber,BarNo

只是预订

与销售相同：

SELECT BatchNumber,SUM(QtyReserved) AS Reserved
FROM   Reservation
GROUP  BY BatchNumber,BarNo

原始库存销售

现在,我们应该能够将前两个查询合并为一个.我不是想优化,只是为了使数据协同工作：

SELECT BarStock.BatchNumber,BarStock.OrgLength,S.SumAvQtySold,(BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
FROM   BarStock
LEFT JOIN (SELECT BatchNumber,Sum(QtySold) AS SumAvQtySold
           FROM   Sales
           GROUP  BY BatchNumber,BarNo) AS S
  ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo)

我们进行LEFT JOIN,因为库存中可能存在尚未售出的棒材.
如果我们做了INNER JOIN,我们会在最终报告中错过这些,这让我们相信这些酒吧从来没有出现在那里.

全部一起

我们现在可以将整个查询包装在另一个LEFT JOIN中,对应保留的条形图以获得最终结果：

SELECT BS.BatchNumber,BS.BarNo,BS.OrgLength,BS.SumAvQtySold,BS.OnStock,R.Reserved,(OnStock - Nz(Reserved)) AS Available
FROM   (SELECT BarStock.BatchNumber,(BarStock.OrgLength - Nz(S.SumAvQtySold)) AS OnStock
        FROM   BarStock
        LEFT JOIN (SELECT BatchNumber,SUM(QtySold) AS SumAvQtySold
                   FROM   Sales
                   GROUP  BY BatchNumber,BarNo) AS S
          ON (BarStock.BatchNumber = S.BatchNumber) AND (BarStock.BarNo = S.BarNo)) AS BS
LEFT JOIN (SELECT BatchNumber,SUM(QtyReserved) AS Reserved
           FROM   Reservation
           GROUP  BY BatchNumber,BarNo) AS R
  ON (BS.BatchNumber = R.BatchNumber) AND (BS.BarNo = R.BarNo)

注意对连接右侧的项使用Nz()：如果给定的BatchNumber / BarNo对没有Sales或Reservation数据,SumAvQtySold和Reserved的值将为Null并将呈现OnStock和Available无论库存中的实际数量如何,这都不是我们预期的结果.

使用Access中的查询设计器,您必须单独创建3个查询,然后将它们组合在一起.
请注意,查询设计在处理多个LEFT和RIGHT联接方面不是很好,所以我认为你不可能一次性写出整个东西.

一些评论

我相信你应该阅读@Remou在评论中给你的信息.
对我来说,这个数据库有一些不幸的设计选择：获取基本库存数据应该像保存库存记录的列上的简单SUM()一样简单.

通常,跟踪库??存的一种简单方法是跟踪每个库存交易：

>进货库存记录有数量
>出货库存记录有 – 数量
>记录还应跟踪零件/项目/条形参考(或ID),交易的日期和时间,以及 – 如果您想管理多个仓库 – 涉及哪个仓库ID.

因此,如果您需要知道所有物品的库存情况,您需要做的就是：

SELECT BarID,Sum(Quantity)
FROM   StockTransaction
GROUP BY BarID

在您的情况下,虽然BatchNumber / BarNo是您的自然键,但将它们保存在单独的Bar表中会有一些优点：

>您可以使用Bar.ID在您需要的任何地方取回Bar.BatchNumber和Bar.BarNo.
>您可以在BarStock,Sales和Reservation表中使用BarID作为外键.它使连接变得更容易,而不必混淆复合键的复杂性.

Access允许的东西并不是很好的做法,例如表名和字段中的空格,最终会使事情变得不那么可读(至少因为你需要将它们保存在[]之间),与VBA变量名不一致代表这些字段,并且与其他数据库不兼容,这些数据库不接受表和字段名称的字母数字字符以外的任何内容(如果您希望稍后调整大小或将数据库与其他应用程序连接).

此外,通过坚持单一命名约定来帮助自己,并保持一致：

>不要不一致地混合使用大写和小写：要么使用CamelCase,要么使用小写或UPPER大小写,但始终遵守该规则.
>以单数形式命名表 – 或复数形式 – 但保持一致.我更喜欢使用单数,如表Part而不是Parts,但它只是一个约定(有其自身的原因).
>拼写正确：收到的不是收到的.在调试为什么某些查询或VBA代码不起作用时,单独这个错误可能会花费你,只是因为某人犯了错字.
>每个表都应该/必须有一个ID列.通常,这将是一个自动增量,保证表中每条记录的唯一性.如果你保持这种惯例,那么外键变得容易猜测和阅读,你永远不必担心一些业务需求改变你可能突然发现自己有2个相同的BatchNumbers的事实,由于某种原因你现在无法理解.

关于数据库设计有很多争论,但是每个人都同意某些“规则”,所以我的建议应该是努力：

>简单性：确保每个表记录一种数据,并且不包含来自其他表的冗余数据(规范化).>一致性：命名约定很重要.无论您选择什么,在整个项目中坚持下去.>清晰度：确保您在3年内和其他人可以轻松阅读表名和字段,并了解它们的内容,而无需阅读300页规范.并不总是那么清楚,但这是值得努力的事情.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!