如何避免对特定列进行分组-PHP中文网问答

文章专题学习下载问答编程词典手游最近更新

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

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

P粉667649253 2023-07-25 14:08:22

362

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

在附带的代码中，我被迫通过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

全部回复 (1)

P粉7446912052023-07-26 10:58:55 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';

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