如何避免將特定列進行分組

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';

根據您的用例，特別是如果您的資料庫很大，您可以使用其他子查詢或索引來進一步優化請求，但它已經相當快了。