如何避免对特定列进行分组

Question

我有一个关于约会的数据表，当更新相关数据时，每个约会可以有多行数据。我想选择每个约会的最后一条记录，以了解每个约会的最新快照。

在附带的代码中，我被迫通过close_pallets和close_units进行分组，这影响了我所看到的(即每次约会返回多行)。我想只按a.appointment_id分组，以便每个约会得到一行。我该怎么做呢?

SELECT
MAX(appointment_record_version_number),
appointment_id,
appointment_pallets AS close_pallets,
appointment_units AS close_units
FROM
b.dh
WHERE
last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
AND warehouse_id = 'xxx'
GROUP BY
appointment_id,
close_pallets,
close_units

Answer 1

您将需要使用子查询来实现这一点。实际上，您需要获取每个约会ID的最大记录版本:

SELECT
    appointment_record_version_number,
    appointment_id,
    appointment_pallets AS close_pallets,
    appointment_units AS close_units
FROM
    b.dh AS t1
WHERE
    t1.appointment_record_version_number = (
        SELECT
            MAX(appointment_record_version_number)
        FROM
            b.dh
        WHERE
            b.dh.data = t1.data
    )
    AND last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
    AND warehouse_id = 'xxx'

你也可以使用JOIN语句来选择最大值，有时会更快:

SELECT
    t1.appointment_record_version_number,
    t1.appointment_id,
    t1.appointment_pallets AS close_pallets,
    t1.appointment_units AS close_units
FROM
    b.dh AS t1
LEFT JOIN b.dh AS t2 ON (
    t1.appointment_record_version_number = t2.appointment_record_version_number
    AND t1.appointment_id < t2.appointment_id
)
WHERE t2.appointment_record_version_number IS NULL
AND last_updated_datetime BETWEEN '2023-06-01' AND '2023-06-30'
AND warehouse_id = 'xxx';

根据您的用例，特别是如果您的数据库很大，您可以使用其他子查询或索引来进一步优化请求，但它已经相当快了。