postgresql 如何将group by语句限制为1列[重复]

y53ybaqx 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(2)|浏览(155)

此问题已在此处有答案：

PostgreSQL: Select only the first record per id based on sort order（1个答案）
16天前关闭
（抱歉，如果这听起来很熟悉，我删除并重新上传了我的问题，因为我标记了错误的SQL版本）
我有一个包含约会数据的表，当关联数据更新时，每个约会可以有多行数据。我想选择每个约会的最后一条记录，以了解每个约会的最新快照。
在附加的代码中，我被迫按close_pallets和close_units分组，这影响了我看到的内容（即它返回多行每个约会）。我只想按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

字符串

postgresql

来源：https://stackoverflow.com/questions/76712016/how-to-limit-group-by-statement-to-1-column

2条答案

按热度按时间

lymnna711#

您可以使用brilliant distinct on with custom ordering而不是group by来实现您的结果。

SELECT DISTINCT ON (appointment_id)
  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'
ORDER BY appointment_id,
         appointment_record_version_number desc; -- pick the biggest one

字符串

赞(0）回复(0）举报 2023-08-04

gdrx4gfi2#

这将给予group by提供所需的输出。

SELECT MAX(appointment_record_version_number),
appointment_id,
MAX(appointment_pallets) AS close_pallets,
MAX(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

字符串

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 如何将group by语句限制为1列[重复]

2条答案

相关问题

热门标签

最新问答