Блог

У меня есть таблицы source и target определено как:

 CREATE TABLE source (id BIGINT PRIMARY KEY);
CREATE TABLE target (id BIGINT PRIMARY KEY, source_id BIGINT REFERENCES source(id), score INT);
 

Я хотел бы получить запрос, который возвращает значение target с наибольшим score значением для каждого source .

Я легко могу получить это target для конкретного source :

 SELECT * FROM target WHERE source_id = ? ORDER BY score DESC LIMIT 1;
 

Но я хотел бы использовать один запрос, чтобы получить наивысшую оценку target для каждого источника.

Самый простой способ, который я нашел для этого, специфичен для PostgreSQL и использует LATERAL ключевое слово:

 SELECT target.*
FROM 
  source, 
  LATERAL (
    SELECT * FROM target
    WHERE source_id = source.id
    ORDER BY score DESC
    LIMIT 1
  ) AS target;
 

Есть ли простой способ сделать это с помощью стандартного SQL? Возможно, используя CTE или GROUP BY пункт?

Вы можете использовать функцию окна

 select * 
from (
    select *,
       row_number() over(partition by source_id order by score desc) rn
    from Target
) t
where rn = 1;
 

Следующий запрос вернет все записи из целевой таблицы с соответствующим максимальным баллом для каждого источника.

     select max(t.score) over (partition by t.source_id) as max_score_per_source, t.* 
    from target t
 

С помощью over() можно указать предложение window, которое работает аналогично функции group by, но не уменьшает количество записей для агрегирования. В вашем случае это лучший подход. Если бы кто-то хотел использовать group by, то также работал бы объединенный дополнительный выбор.