Как мне присоединить самую последнюю строку в одной таблице к другой таблице?

У меня есть данные, которые выглядят так:

entities
id         name
1          Apple
2          Orange
3          Banana

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

scores 
id  entity_id    score   date_added
1    1            10       1/2/09
2    2            10       1/2/09
3    1            15       1/3/09
4    2            10       1/03/09
5    1            15       1/4/09
6    2            15       1/4/09
7    3            22       1/4/09

Я хочу иметь возможность выбрать все объекты вместе с последними записанными баллами для каждого, что приведет к таким данным:

entities
id name     score  date_added
1  Apple     15     1/4/09
2  Orange    15     1/4/09
3  Banana    15     1/4/09

Я могу получить данные для одного объекта, используя этот запрос:

SELECT entities.*, 
       scores.score, 
       scores.date_added 
FROM entities

INNER  JOIN scores
ON entities.id = scores.entity_id

WHERE entities.id = ?

ORDER BY scores.date_added DESC
LIMIT 1

Но я не понимаю, как выбрать одно и то же для всех сущностей. Может, это смотрит мне в лицо?

Большое спасибо за то, что нашли время.

Спасибо за отличные ответы. Я дам ему несколько дней, чтобы посмотреть, не появится ли предпочтительное решение, а затем выберу ответ.

ОБНОВЛЕНИЕ: я пробовал несколько из предложенных решений, основная проблема, с которой я столкнулся сейчас, заключается в том, что если у объекта еще нет сгенерированной оценки, они не отображаются в списке.

Как будет выглядеть SQL, чтобы гарантировать, что все объекты будут возвращены, даже если у них еще нет оценок?

ОБНОВЛЕНИЕ: ответ выбран. Спасибо всем!


sql date join greatest-n-per-group
person GloryFish    schedule 30.01.2009    source источник

Ответы (7)


arrow_upward
63
arrow_downward

Я так делаю:

SELECT e.*, s1.score, s1.date_added 
FROM entities e
  INNER JOIN scores s1
    ON (e.id = s1.entity_id)
  LEFT OUTER JOIN scores s2
    ON (e.id = s2.entity_id AND s1.id < s2.id)
WHERE s2.id IS NULL;
person Bill Karwin    schedule 30.01.2009
comment
Спасибо, Билл, я остановился на этом решении, но заменил INNER JOIN на LEFT JOIN, чтобы включить объекты, у которых еще нет оценок. - person GloryFish; 13.02.2009
comment
Мне нравится это решение, я также использую LEFT JOIN. Как бы вы порекомендовали справиться с ничьей, если у одного и того же лица было два балла с одной и той же датой? - person russds; 08.11.2012
comment
Я пытался настроить запрос в соединении и на основе поля даты, чтобы получить результат для самой последней записи, но не присоединиться к записи с будущей датой. Мне пришлось выполнить подзапрос как во внутреннем соединении, так и в левом внешнем соединении, выбирая записи, в которых поле даты было ‹= GETDATE (). У меня это сработало. - person user1408767; 13.02.2015
comment
@BillKarwin, какова цель WHERE s2.id IS NULL? - person dev1998; 19.05.2016
comment
@ dev1998, s2.id будет NULL только в том случае, если OUTER JOIN не обнаружил в s2 строки, соответствующей условиям. То есть нет строки с таким же entity_id и id большим, чем s1.id. Если такой строки не существует, это должно означать, что s1 имеет наибольший идентификатор для этой сущности. - person Bill Karwin; 19.05.2016

arrow_upward
10
arrow_downward

Просто чтобы добавить свой вариант:

SELECT e.*, s1.score
FROM entities e
INNER JOIN score s1 ON e.id = s1.entity_id
WHERE NOT EXISTS (
    SELECT 1 FROM score s2 WHERE s2.id > s1.id
)
person Ray Hidayat    schedule 31.01.2009
comment
Мне нравится этот! По крайней мере, на SQL Server это будет работать очень быстро. Теперь я бы предложил изменить ВНУТРЕННИЙ на ЛЕВОЕ СОЕДИНЕНИЕ, на всякий случай, если новый объект был только что добавлен, а процесс еще не запущен. - person Joe Pineda; 31.01.2009
comment
Для дополнительной скорости вы можете включить проверку существования как часть условий присоединения. По крайней мере, в SQL S. они выполняются до того, как будет выполнена фильтрация WHERE, поэтому вы сэкономите несколько миллисекунд на строку, сократив свой поиск там. - person Joe Pineda; 31.01.2009
comment
Хммм, фильтрация WHERE не обязательно выполняется после предложений JOIN. Фактически, они могут быть выполнены первыми, особенно если предложение WHERE фильтрует по индексу ... - person MatBailie; 31.01.2009

arrow_upward
5
arrow_downward

подход 1

SELECT entities.*, 
       scores.score, 
       scores.date_added 
FROM entities

INNER  JOIN scores
ON entities.id = scores.entity_id

WHERE scores.date_added = 
  (SELECT max(date_added) FROM scores where entity_id = entities.id)
person Michael Buen    schedule 31.01.2009
comment
Лучшая (на сегодняшний день) производительность, если [scores] проиндексировано [entity_id] - person MatBailie; 31.01.2009
comment
В моих тестах это решение, по-видимому, возвращает несколько строк для любого объекта, для которого в одну и ту же (самую последнюю) дату было добавлено более одного балла. - person beporter; 22.09.2011
comment
Также будут пропущены строки без оценки. - person Coleman; 30.03.2017

arrow_upward
4
arrow_downward

Я знаю, что это старый вопрос, просто подумал, что добавлю подход, о котором еще никто не упоминал, Cross Apply или Outer Apply. Они доступны в SQL Server 2005 (тип базы данных не помечен в этом вопросе) или выше.

Использование временных таблиц

DECLARE @Entities TABLE(Id INT PRIMARY KEY, name NVARCHAR(MAX))
INSERT INTO @Entities
VALUES (1, 'Apple'), (2, 'Orange'), (3, 'Banana'), (4, 'Cherry')

DECLARE @Scores TABLE(Id INT PRIMARY KEY, Entity_Id INT, Score INT, Date_Added DATE)
INSERT INTO @Scores
VALUES (1,1,10,'2009-02-01'),
(2,2,10,'2009-02-01'),
(3,1,15,'2009-02-01'),
(4,2,10,'2009-03-01'),
(5,1,15,'2009-04-01'),
(6,2,15,'2009-04-01'),
(7,3,22,'2009-04-01')

Вы могли бы использовать

SELECT E.Id, E.name, S.Score, S.Date_Added 
FROM @Entities E
CROSS APPLY
(
    SELECT TOP 1 * 
    FROM @Scores Sc 
    WHERE Sc.Entity_Id = E.Id  
    ORDER BY sc.Score DESC
) AS S

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

SELECT E.Id, E.name, S.Score, S.Date_Added 
FROM @Entities E
OUTER APPLY
(
    SELECT TOP 1 * 
    FROM @Scores Sc 
    WHERE Sc.Entity_Id = E.Id  
    ORDER BY sc.Score DESC
) AS S
person Manatherin    schedule 10.10.2012

arrow_upward
3
arrow_downward

подход 2

стоимость запроса относительно пакета:

SELECT entities.*, 
       scores.score, 
       scores.date_added 
FROM entities

INNER  JOIN scores
ON entities.id = scores.entity_id

inner join 
    (
    SELECT 
           entity_id, max(date_added) as recent_date
    FROM scores
    group by entity_id
    ) as y on entities.id = y.entity_id and scores.date_added = y.recent_date
person Michael Buen    schedule 31.01.2009

arrow_upward
1
arrow_downward

Вы также можете сделать это сегодня в большинстве СУБД (Oracle, PostgreSQL, SQL Server) с естественным запросом с использованием оконных функций, таких как ROW_NUMBER:

SELECT id, name, score, date_added FROM (
 SELECT e.id, e.name, s.score, s.date_added,
 ROW_NUMBER() OVER (PARTITION BY e.id ORDER BY s.date_added DESC) rn
 FROM Entities e INNER JOIN Scores s ON e.id = s.entity_id
) tmp WHERE rn = 1;

SQL Fiddle

person Cristi S.    schedule 23.12.2014

arrow_upward

arrow_downward

person    schedule
comment
В вашем подзапросе используется столбец (date_added), которого нет в запрашиваемой таблице. - person Tom H; 31.01.2009