如何避免將特定列進行分組
P粉667649253
P粉667649253 2023-07-25 14:08:22
0
1
363

我有一個關於約會的資料表,當更新相關資料時,每個約會可以有多行資料。我想選擇每個約會的最後一筆記錄,以了解每個約會的最新快照。

在附帶的程式碼中,我被迫透過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


P粉667649253
P粉667649253

全部回覆 (1)
P粉744691205

您將需要使用子查詢來實現這一點。實際上,您需要取得每個約會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';

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

    最新下載
    更多>
    網站特效
    網站源碼
    網站素材
    前端模板
    關於我們 免責聲明 Sitemap
    PHP中文網:公益線上PHP培訓,幫助PHP學習者快速成長!