总和并使用SSMS加入SQL（数量弄乱）

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

---

想要改进此问题吗？添加详细信息并通过

闭合 2年前。

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'

for 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