sql - SQL 重复值 1 最大值 2

Question

我对 SQL 很陌生，我的问题与此处发布的问题非常相似。但是，我的用户名和日期在两个不同的数据库中。另外，我希望输出 Date 是它可能的最大值。

SQL Select 使一个值只出现一次

SELECT
PART.CUSTOMER_PART_NUMBER,

CAST(CASE WHEN WO.PARTS_PER_PANEL * WOM.[QUAN_BOM] = 0 THEN NULL --casts to INT & in case, checks off divide by 0
ELSE WO.QUAN_SCH / NULLIF(WO.PARTS_PER_PANEL * WOM.[QUAN_BOM],0)
END AS INT) AS TotalRequired,

Convert(INT,IPA.[CONSIGN_ONHAND_QTY] + IPA.[QUAN_ON_HAND] + IPA.[QUAN_ON_ORDER] - (WO.QUAN_SCH / NULLIF(WO.PARTS_PER_PANEL,0) * WOM.[QUAN_BOM])) AS [Difference]

FROM
…
WHERE
  PART.CUSTOMER_PART_NUMBER LIKE '%512255%'

输出应该是这样的。相反，有 4 个输出。

CUSTOMER_PART_NUMBER....TotalRequired...OnHand
1212255   …              MAX() …    MAX()

标签： sqlsql-server

Answer 1

您可以为此使用内部查询 - 因为您已经有了结果，但只是在多行中。所以一个简单的解决方案是这样的：

SELECT
  inner_result.CUSTOMER_PART_NUMBER,
  ... (your other fields)
  MAX(inner_result.TotalRequired) as [TotalRequired],
  ... (your other fields wrapped with MAX())
  MAX(inner_result.Difference) as [Difference]
FROM (
  SELECT
    PART.CUSTOMER_PART_NUMBER,
    CAST(CASE
      WHEN WO.PARTS_PER_PANEL * WOM.[QUAN_BOM] = 0 THEN NULL --casts to INT & in case,
      ELSE WO.QUAN_SCH / NULLIF(WO.PARTS_PER_PANEL * WOM.[QUAN_BOM],0)
    END AS INT) AS TotalRequired,
    CONVERT(INT,
      IPA.[CONSIGN_ONHAND_QTY] +
      IPA.[QUAN_ON_HAND] +
      IPA.[QUAN_ON_ORDER] -
      (WO.QUAN_SCH / NULLIF(WO.PARTS_PER_PANEL,0) * WOM.[QUAN_BOM])
    ) AS [Difference]
  FROM ...
  WHERE
    PART.CUSTOMER_PART_NUMBER LIKE '%512255%') as inner_result
GROUP BY
  inner_result.CUSTOMER_PART_NUMBER,
  ... (your other fields that are not involved in a MAX() function)

如果您在使用此解决方案时遇到问题，请提供整个查询 - 我可以为您编辑它。