使用 SSMS 在 SQL 中求和和连接(数量搞砸了)

SQL

我在加入多个表时遇到问题。当我将另一个联接添加到正确的查询数量时,就会搞砸。我加入了状态表,因为我想从那里获取状态。(使用 SSMS)

这是正确的代码和实际正确的数量:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0

以正确数量执行的正确代码
以正确数量执行的正确代码

现在,当我尝试加入另一张桌子时,我仍然希望获得与上面显示的相同数量。

这是我尝试过的,但数量似乎已乘以 9 或其他东西。也可能是我的连接错误或者我犯了一个愚蠢的错误。

这是新代码(加入Part_Location表格),但我的数量不正确(数量应与图 1 中所示的相同)

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  L.STATUS,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =     IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
INNER JOIN PART_LOCATION L ON L.PART_ID = TIT.PART_ID 
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
  AND L.STATUS = 'A'
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  L.STATUS
HAVING
  SUM(TIT.QTY) > 0

在添加第 6、17、21 和 32 行但现在数量不正确的地方进行了更改
在添加第 6、17、21 和 32 行但现在数量不正确的地方进行了更改

卡齐·穆罕默德·阿里·努尔

您可以使用子查询而不是联接。如果任何 L.PART_ID 有多个状态,则查询将显示错误。您需要根据您的 dbms 更改子查询。

对于 sql 服务器:

select top 1 STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'

对于 MySql:

select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A' Limit 1

询问:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  (select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'),
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章