更新时间:2023-01-29 20:39:01
在 Postgres 中,这可以通过以下查询来实现.
In Postgres, this can be achieved with the following query.
SELECT DISTINCT ON ("group") * FROM projects
ORDER BY "group", date DESC, id DESC
因为 date
列在这里可能不是唯一的,我在 id DESC
上添加了一个额外的 ORDER BY
子句以打破关系具有较高 ID 的记录,以防一组中的两个记录具有相同的日期.您可能想要使用另一列,例如上次更新的日期/时间等,这取决于您的用例.
Because the date
column might not be unique here, I have added an additional ORDER BY
clause on id DESC
to break ties in favor of the record with the higher ID, in case two records in a group have the same date. You might instead want to use another column like the date/time of the last update or so, that depends on your use case.
继续,遗憾的是 ActiveRecord 没有用于 DISTINCT ON
的 API,但我们仍然可以使用带有 select
的普通 SQL:
Moving on, ActiveRecord unfortunately has no API for DISTINCT ON
, but we can still use plain SQL with select
:
Project.select('DISTINCT ON ("group") *').order(:group, date: :desc, id: :desc)
或者如果您更喜欢使用 ARel 而不是原始 SQL:
or if you prefer using ARel instead of having raw SQL:
p = Project.arel_table
Project.find_by_sql(
p.project(p[Arel.star])
.distinct_on(p[:group])
.order(p[:group], p[:date].desc, p[:id].desc)
)
对于像 MySQL 这样的其他数据库,不幸的是,这并不方便.有多种解决方案可用,例如参见这个答案.