mysql - 如果返回多行，则返回日期差异最大的行

Question

我有一个相当长的查询，如下所示:

SELECT DISTINCT
        `pro`.`productId` AS `productId`,
        `c`.`lastName` AS `lastName`,
        (`b`.`bookingEnd` - INTERVAL IFNULL(`po`.`pickingOffsetMinutes`, 0) MINUTE) AS `pickAt`

    FROM
        ((((((((((`pk-booking-service`.`booking` `b`
        LEFT JOIN `pk-booking-service`.`flight` `f` ON (((`b`.`bookingId` = `f`.`bookingId`)
            AND (`f`.`flightDirectionId` = 2))))
        LEFT JOIN `pk-booking-service`.`customer` `c` ON ((`c`.`bookingId` = `b`.`bookingId`)))
        LEFT JOIN `pk-booking-service`.`customerVehicle` `cv` ON ((`b`.`bookingId` = `cv`.`bookingId`)))
        LEFT JOIN `pk-operation-service`.`PickingOffset` `po` ON ((`b`.`bookingId` = `po`.`bookingId`)))
        LEFT JOIN `pk-booking-service`.`vehicle` `v` ON ((`cv`.`vehicleId` = `v`.`vehicleId`)))
        LEFT JOIN `pk-asset-service`.`asset` `a` ON (((`b`.`bookingId` = `a`.`bookingId`)
            AND (`a`.`assetTypeId` = 2))))
        LEFT JOIN `pk-location-service`.`assetLocationState` `als` ON ((`a`.`assetId` = `als`.`assetId`)))
        LEFT JOIN `pk-location-service`.`location` `loc` ON ((`als`.`locationId` = `loc`.`locationId`)))
        LEFT JOIN `pk-product-service`.`bookingProduct` `pro` ON ((`pro`.`bookingId` = `b`.`bookingId`)))
        JOIN `pk-location-service`.`locationCluster` `locC` ON ((`locC`.`locationClusterId` = `loc`.`locationClusterId`)))
    WHERE
        ((`b`.`bookingStatusId` <> 2)
            AND (`b`.`checkingStatusId` = 2)
            AND (`pro`.`archived` = 0)
            AND  `b`.`siteId` = 24
            AND (`b`.`bookingEnd` >= (CURDATE() + INTERVAL -(1) SECOND))
            AND (`b`.`bookingEnd` <= (CURDATE() + INTERVAL 3 DAY)))

结果如下:

productId .         lastName .  pickAt
661,                FOX,        2020-01-11 22:22:00
661,                Demo,       2020-01-01 11:11:00
884,                Demo,       2020-01-03 11:11:00
897,                Demo,       2020-01-02 11:11:00
926,                Demo,       2020-01-05 11:11:00

我正在寻找的期望结果是:

productId .         lastName .  pickAt
661,                FOX,        2020-01-11 22:22:00
661,                Demo,       2020-01-01 11:11:00

它选择从 pickAt 日期时间到现在的最大时间间隔。

我进行了多次尝试，例如使用 MAX 并更改我的查询以适应上述内容，但没有成功。有建议吗？

Answer 1

使用GROUP BY而不是DISTINCT:

SELECT 
        `pro`.`productId` AS `productId`,
        `c`.`lastName` AS `lastName`,
        MIN(`b`.`bookingEnd` - INTERVAL IFNULL(`po`.`pickingOffsetMinutes`, 0) MINUTE) `pickAt`
FROM ...
WHERE ...
GROUP BY 
    `pro`.`productId` AS `productId`, 
    `c`.`lastName` AS `lastName`