SQL SERVER:(性能问题)获取所有行，如果列重复，则从select语句中选择另一列的最大行

Question

如果PLANT列值重复并且工作正常，我已经编写了以下代码来获得最大ACUTAL_SHIP_DATE值。

SELECT A.PLANT,
A.SLOT_NUM,
A.CONSUMPTIONENDITEM,
A.SALESORDERNUMBER,
A.SOLINEITEM,
A.ACUTAL_SHIP_DATE
FROM (SELECT TOP 10
P.SLOT_NUM,
P.PLANT,
P.CONSUMPTIONENDITEM,
P.SALESORDERNUMBER,
P.SOLINEITEM,
T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
FROM SRTPROJECT P WITH (NOLOCK)
INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
AND P.PLANT = T.PLANT
AND P.CONSUMPTIONENDITEM = T.MAT_NUM) A
INNER JOIN (SELECT DI.PLANT,
MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE
FROM (SELECT TOP 10
P.SLOT_NUM,
P.PLANT,
P.CONSUMPTIONENDITEM,
P.SALESORDERNUMBER,
P.SOLINEITEM,
T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
FROM SRTPROJECT P WITH (NOLOCK)
INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
  AND P.PLANT = T.PLANT
  AND P.CONSUMPTIONENDITEM = T.MAT_NUM) DI
GROUP BY DI.PLANT) B ON A.PLANT = B.PLANT
AND A.ACUTAL_SHIP_DATE = B.ACUTAL_SHIP_DATE;

在上面的查询中步骤1:从两个表中提取数据并进行收集。

SELECT TOP 10
P.SLOT_NUM,
P.PLANT,
P.CONSUMPTIONENDITEM,
P.SALESORDERNUMBER,
P.SOLINEITEM,
T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
FROM SRTPROJECT P WITH (NOLOCK)
INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
AND P.PLANT = T.PLANT
AND P.CONSUMPTIONENDITEM = T.MAT_NUM;

步骤2：如果PLANT列值重复，则应用最大值

(SELECT TOP 10 P.SLOT_NUM, P.PLANT, P.CONSUMPTIONENDITEM, P.SALESORDERNUMBER, P.SOLINEITEM,T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE FROM SRTPROJECT P WITH (NOLOCK) INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM AND P.PLANT = T.PLANT AND P.CONSUMPTIONENDITEM = T.MAT_NUM) AS A INNER JOIN (Select DI.PLANT, MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE From (SELECT TOP 10 P.SLOT_NUM, P.PLANT, P.CONSUMPTIONENDITEM, P.SALESORDERNUMBER, P.SOLINEITEM, T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE FROM SRTPROJECT P WITH (NOLOCK) INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM AND P.PLANT T.PLANT AND P.CONSUMPTIONENDITEM = T.MAT_NUM) AS DI GROUP BY DI.PLANT)

步骤3：然后选择最终查询所需的所有列，该查询最初写入

但问题是我使用了两次相同的子查询。有没有办法删除重复的代码。

Answer 1

我认为这是你想要的，但我没有测试它，因为你没有提供样本数据：

with cte 
as
(
SELECT TOP 10
P.SLOT_NUM,
P.PLANT,
P.CONSUMPTIONENDITEM,
P.SALESORDERNUMBER,
P.SOLINEITEM,
T.SHIP_ACTUAL_DATE AS ACUTAL_SHIP_DATE
FROM SRTPROJECT P WITH (NOLOCK)
INNER JOIN V_SRT_TOOLINFO_CM T WITH (NOLOCK) ON P.SLOT_NUM = T.SLOT_NUM
AND P.PLANT = T.PLANT
AND P.CONSUMPTIONENDITEM = T.MAT_NUM
)
SELECT A.PLANT,
A.SLOT_NUM,
A.CONSUMPTIONENDITEM,
A.SALESORDERNUMBER,
A.SOLINEITEM,
A.ACUTAL_SHIP_DATE
FROM cte A
INNER JOIN (SELECT DI.PLANT,
MAX(DI.ACUTAL_SHIP_DATE) AS ACUTAL_SHIP_DATE
FROM cte DI
GROUP BY DI.PLANT) B ON A.PLANT = B.PLANT
AND A.ACUTAL_SHIP_DATE = B.ACUTAL_SHIP_DATE;

它只是使用WITH来创建"；cte"；因此，您不必执行同一查询两次。

您应该尽量提供最少的可复制示例，包括示例数据和预期结果。这样会更容易帮助你