Как получить top-n, используя встроенное представление при выполнении соединения с таблицами в основном запросе в Oracle / SQL?

Question

Кажется, что это действительно простой запрос, но я должен упустить что-то очевидное. Я предполагаю, что этот сценарий довольно распространен.

У меня есть таблица, в которой хранятся продукты, и другая таблица, в которой хранятся транзакции. Таблица транзакций содержит информацию о продавце. Теперь мне нужно подготовить отчет, в котором перечислены все продукты и указан идентификатор продавца, который продал этот продукт последним.

Я попытался простой запрос, как:

выберите id, product_name, (выберите salesman_id из (выберите salesman_id из trans, где product_id = a.id заказ по trans_date desc), где rownum = 1) salesman_id из продуктов a

Этот запрос дал мне неверный идентификатор на a.id.

поэтому после поиска в сети я попытался добавить к нему предложение LATERAL. как следует

выберите id, product_name, salesman_id из продуктов a, LATERAL(выберите salesman_id from (выберите salesman_id из trans, где product_id = a.id заказ по trans_date desc), где rownum = 1)

но этот запрос дает мне неполный оператор SQL.

Есть ли простой способ добиться этого в одном запросе?

Answer 1

select id, product_name, salesman_id from
(select p.id, p.product_name, t.salesman_id, row_number() over(partition by p.id order by t.trans_date desc) rw
  from products p left join trans t on t.product_id = p.id
) where rw = 1;

1. Присоединяйтесь к таблицам продуктов и транс
2. Некоторые продукты могут не иметь транзакций. Поскольку вам нужно показать все продукты, которые вам нужны, LEFT JOIN
3. Используйте аналитические функции для расчета номера заказа (row_number) каждой транзакции для каждого продукта (номер заказа начинается с 1 для каждого продукта)

Answer 2

Вам просто нужен подзапрос, что-то вроде:

выберите p.id, p.product_name, t.salesman_id из продуктов p, trans t, где t.product_id = p.product_id и t.trans_date = (выберите max(trans_date) из trans t_in, где t_in.product_id = t.product_id)