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

Question

我在加入多张桌子上有个问题。当我将另一个联接添加到正确的查询量时，会造成混乱。我加入了status表，因为我想从那里获得状态。(使用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表)，但我的数量不正确(QTY应该与图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行添加但现在QTY不正确的地方所作的更改

​

Answer 1

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

对于sql server：

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