代码之家 › 专栏 › 技术社区 › svoop

仅查询组中具有最大值的记录

activerecord postgresql ruby-on-rails

2

svoop · 技术社区 · 8 年前

假设你有 users PostgreSQL上的表:

id | group_id |    name | age
---|----------|---------|----
 1 |        1 |    adam |  10
 2 |        1 |     ben |  11
 3 |        1 | charlie |  12   <-
 3 |        2 |  donnie |  20  
 4 |        2 |    ewan |  21   <-
 5 |        3 |    fred |  30   <-

group_id (那些标有箭头的)?

group by ,但是继续打 "users.id" must appear in the GROUP BY clause .

(注意:我必须在Rails AR模型范围内处理查询。)

3 回复 | 直到 8 年前

1

MrYoshiji 8 年前

DISTINCT ON (col)

select distinct on (users.group_id) users.*
from users
order by users.group_id, users.age desc;
-- you might want to add extra column in ordering in case 2 users have the same age for same group_id

翻译成Rails,它将是:

User
  .select('DISTINCT ON (users.group_id), users.*')
  .order('users.group_id, users.age DESC')

DISTINCT ON : https://www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT

https://www.db-fiddle.com/f/t4jeW4Sy91oxEfjMKYJpB1/0

2

1

Lukasz Szozda 8 年前

你可以用 ROW_NUMBER/RANK (如果可能)窗口函数:

SELECT * 
FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY group_id ORDER BY age DESC) AS rn
      FROM tab) s
WHERE s.rn = 1;

3

1

ScaisEdge 8 年前

select m.* 
from  users m
inner join (
  select  group_id, max(age) max_age
  from users 
  group by group_id
) AS t on (t.group_id = m.group_id and t.max_age  = m.age)