sql - 基于多个约束的最大值

Question

ProductCode我有一个包含, OrderType, OrderLineWeight, Price1, FreightCost,Price2和列的表LoadOutDate。

我在下面添加了另一列，它为它们提供了一个相对于其代码的行号 (ls)，这使它们ProductCode根据日期按 1-n 的顺序排列（下一个销售日期在前）。

数据是远期销售/管道。我要做的是返回下一个日期的最大值，每个代码都有远期销售。如果下一个日期只有 1 个，我想要那一行。如果该日期有 2 个或更多，我想要最高的行OrderLineWeight。

我必须到这里（下），它根据最大值返回行，OrderLineWeight但无法计算出如何返回“最小”日期的最大重量。

SELECT 
         ProductCode
        ,OrderType
        ,OrderLineWeight
        ,Price1
        ,FreightCost
        ,Price2
        ,LoadOutDate
        ,row_number() OVER (PARTITION BY ProductCode ORDER BY LoadOutDate ASC )         
        ) AS ls

FROM [TEST].[dbo].[DataBaseName]
INNER JOIN
(
  SELECT ProductCode, MAX(OrderLineWeight) CodeMaxWeight
  FROM DataBaseName
  GROUP BY ProductCode
) MaxOrderLineWeight 
ON MaxOrderLineWeight.ProductCode = DataBaseName.ProductCode
AND MaxOrderLineWeight.CodeMaxWeight = DataBaseName.OrderLineWeight

ORDER BY ProductCode 

当前结果：

ProductCode,OrderType,OrderLineWeight,Price1,FreightCost,Price2,LoadOutDate,ls
0111,0,1644.9200,4.5882352,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0117,0,18.3170,7.0588235,0.0000,0.0000000000,2021-04-23 00:00:00.000,1
0157,1,14.2180,3.4411764,0.0000,6.1824852331,2021-05-07 00:00:00.000,1
0158,1,19.5810,3.0588235,0.0000,5.4922038375,2021-05-07 00:00:00.000,1
0160,1,9.7800,3.4470588,0.0000,6.1931247019,2021-04-23 00:00:00.000,1
0160,1,10.6680,3.4470588,0.0000,6.1931247019,2021-04-30 00:00:00.000,2
0160,1,10.7650,3.4470588,0.0000,6.1931247019,2021-05-07 00:00:00.000,3
0163,1,11.5800,5.7352941,0.0000,10.3243570458,2021-04-23 00:00:00.000,1
0163,1,11.3800,5.7352941,0.0000,10.3243570458,2021-04-30 00:00:00.000,2
0163,1,11.2890,5.7352941,0.0000,10.3243570458,2021-05-07 00:00:00.000,3
0165,1,14.2730,3.3823529,0.0000,6.0763106724,2021-05-07 00:00:00.000,1
0166,1,12.2800,2.6000000,0.0000,4.6638294749,2021-04-23 00:00:00.000,1
0166,1,17.2670,2.6000000,0.0000,4.6638294749,2021-04-30 00:00:00.000,2
0166,1,17.3930,2.6000000,0.0000,4.6638294749,2021-05-07 00:00:00.000,3
0180,0,18.0150,1.4705882,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0181,0,37.9580,2.3529411,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0182,1,6244.3000,0.8823529,0.0000,0.5200498954,2021-03-31 00:00:00.000,1
0182,1,7560.8000,0.8823529,0.0000,0.5200498954,2021-03-31 00:00:00.000,2
0182,1,4000.0000,1.0117647,0.0000,0.6007631067,2021-05-31 00:00:00.000,3
0182,1,4700.0000,0.8823529,0.0000,0.5200498954,2021-05-31 00:00:00.000,4
0182,1,5000.0000,0.8823529,0.0000,0.5200498954,2021-05-31 00:00:00.000,5
0182,1,5000.0000,0.9882352,0.0000,0.5860879774,2021-05-31 00:00:00.000,6
0182,1,6000.0000,1.0000000,0.0000,0.5934255420,2021-05-31 00:00:00.000,7
0185,1,1970.3600,0.6470588,0.0000,0.3732986021,2021-03-31 00:00:00.000,1
0185,1,2000.0000,0.7176470,0.0000,0.4173239901,2021-05-31 00:00:00.000,2
0185,1,5000.0000,0.6470588,0.0000,0.3732986021,2021-05-31 00:00:00.000,3
0185,1,1000.0000,0.6470588,0.0000,0.3732986021,2021-05-31 00:00:00.000,4
0186,1,622.2000,1.0000000,0.0000,0.5934255420,2021-03-31 00:00:00.000,1
0186,1,1000.0000,1.0000000,0.0000,0.5934255420,2021-05-31 00:00:00.000,2
0186,1,3000.0000,1.1176470,0.0000,0.6668011886,2021-05-31 00:00:00.000,3
0186,1,500.0000,1.0000000,0.0000,0.5934255420,2021-05-31 00:00:00.000,4
0187,1,794.8400,0.7647058,0.0000,0.4466742488,2021-03-31 00:00:00.000,1
0187,1,1000.0000,0.7647058,0.0000,0.4466742488,2021-05-31 00:00:00.000,2
0187,1,5000.0000,1.1176470,0.0000,0.6668011886,2021-05-31 00:00:00.000,3
0188,1,2449.7600,0.5882352,0.0000,0.3366107788,2021-03-31 00:00:00.000,1
0188,1,5000.0000,0.6470588,0.0000,0.3732986021,2021-05-31 00:00:00.000,2
0188,1,5000.0000,0.7647058,0.0000,0.4466742488,2021-05-31 00:00:00.000,3
0188,1,1000.0000,0.6470588,0.0000,0.3732986021,2021-05-31 00:00:00.000,4
0189,1,440.0600,1.1764705,0.0000,0.7034890119,2021-03-31 00:00:00.000,1
0189,1,800.0000,1.1764705,0.0000,0.7034890119,2021-05-31 00:00:00.000,2
0189,1,1000.0000,1.1764705,0.0000,0.7034890119,2021-05-31 00:00:00.000,3
0189,1,3000.0000,1.7647058,0.0000,1.0703672451,2021-05-31 00:00:00.000,4
0190,1,1000.0000,1.7647058,0.0000,1.0703672451,2021-05-31 00:00:00.000,1
0190,1,5000.0000,2.0000000,0.0000,1.2171185383,2021-05-31 00:00:00.000,2
0191,0,79.5990,11.7647058,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0200,1,19.0000,52.9411764,7184.5800,30.6073669149,2021-03-29 00:00:00.000,1

预期结果：

ProductCode,OrderType,OrderLineWeight,Price1,FreightCost,Price2,LoadOutDate,ls
0111,0,1644.9200,4.5882352,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0117,0,18.3170,7.0588235,0.0000,0.0000000000,2021-04-23 00:00:00.000,1
0157,1,14.2180,3.4411764,0.0000,6.1824852331,2021-05-07 00:00:00.000,1
0158,1,19.5810,3.0588235,0.0000,5.4922038375,2021-05-07 00:00:00.000,1
0160,1,9.7800,3.4470588,0.0000,6.1931247019,2021-04-23 00:00:00.000,1
0163,1,11.5800,5.7352941,0.0000,10.3243570458,2021-04-23 00:00:00.000,1
0165,1,14.2730,3.3823529,0.0000,6.0763106724,2021-05-07 00:00:00.000,1
0166,1,12.2800,2.6000000,0.0000,4.6638294749,2021-04-23 00:00:00.000,1
0180,0,18.0150,1.4705882,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0181,0,37.9580,2.3529411,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0182,1,7560.8000,0.8823529,0.0000,0.5200498954,2021-03-31 00:00:00.000,2
0185,1,1970.3600,0.6470588,0.0000,0.3732986021,2021-03-31 00:00:00.000,1
0186,1,622.2000,1.0000000,0.0000,0.5934255420,2021-03-31 00:00:00.000,1
0187,1,794.8400,0.7647058,0.0000,0.4466742488,2021-03-31 00:00:00.000,1
0188,1,2449.7600,0.5882352,0.0000,0.3366107788,2021-03-31 00:00:00.000,1
0189,1,440.0600,1.1764705,0.0000,0.7034890119,2021-03-31 00:00:00.000,1
0190,1,5000.0000,2.0000000,0.0000,1.2171185383,2021-05-31 00:00:00.000,2
0191,0,79.5990,11.7647058,0.0000,0.0000000000,2021-04-21 00:00:00.000,1
0200,1,19.0000,52.9411764,7184.5800,30.6073669149,2021-03-29 00:00:00.000,1

标签： sqlsql-servertsql