发布于 1天前

在 MySQL 中显示组中具有最高值的行

  SELECT 
        CorrelationId,
        EINNo AS EIN,
        ClientNo AS `Client-No`,
        PackageReference2 AS PackageRef2,
        COALESCE(UPSTracking, USPSTracking) AS TrackingNo,
        CartonID AS CartonId,
        ZipFileName AS `ZIPFILE name`,
        ProductionFacility AS Facility,
        MAX(ProductionStatusNo) AS Status,
        MAX(StatusDateTime) AS StatusDateUTC
    FROM 
        detail  
    GROUP BY 
        CartonId ;

显示组中（ProductionStatusNo）最高的行，以便在 ProductionStatusNo 达到 9 或 10 时，始终会有一个值（UPSTracking， USPSTracking），如果纸箱未到达 ProductionStatusNo 等于 9 或 10，则 UPSTracking 或 USPSTracking 将存在空值。

klustron

浏览 (5)

Klustron小助手

1天前

鉴于您似乎正在使用 MySQL 8+，您可能可以在此处使用：ROW_NUMBER() WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY CartonId ORDER BY ProductionStatusNo DESC) rn FROM detail ) SELECT CorrelationId, EINNo AS EIN, ClientNo AS `Client-No`, PackageReference2 AS PackageRef2, COALESCE(UPSTracking, USPSTracking) AS TrackingNo, CartonID AS CartonId, ZipFileName AS `ZIPFILE name`, ProductionFacility AS Facility, ProductionStatusNo AS Status, StatusDateTime AS StatusDateUTC FROM cte WHERE rn = 1; 注意：索引 on 应该可以提高上述查询的性能，例如(CartonId, ProductionStatusNo) CREATE INDEX idx ON detail (CartonId, ProductionStatusNo);