Поиск совпадающих записей между 2 таблицами SQL

#sql

Вопрос:

У меня есть одна таблица, содержащая элемент, и основной поставщик, у которого мы приобрели элемент.

 Table 1
-------
item id
supplier id

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

Элемент может быть приобретен у нескольких поставщиков, поэтому элемент может иметь несколько записей в таблице # 2, либо с одним и тем же идентификатором поставщика, либо с разными идентификаторами поставщика.

 po #
item id
supplier id
date purchased

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

Это код SQL, который я придумал, но он не возвращает результаты, которые я ожидаю.

 SELECT 
    K.item_ID, 
    MAX(PurchaseInfo.order_date) AS [Last Purchase Date],
    PurchaseInfo.po_no
FROM 
    Items K
LEFT OUTER JOIN
    (SELECT
         order_date, supplier_id, po_no, item_id 
     FROM
         Purchases
     GROUP BY 
         order_date, supplier_id, po_no, item_id) AS PurchaseInfo ON PurchaseInfo.item_id = K.item_id 
                                                                  AND PurchaseInfo.supplier_id = K.supplier_id
GROUP BY 
    PurchaseInfo.order_date, K.item_id, PurchaseInfo.po_no

Ответ №1:

Одним из подходов является коррелированный подзапрос:

 select i.item_id, i.supplier_id,
       (select max(pi.order_date)
        from purchase_info pi 
        where pi.item_id = i.item_id and
              pi.supplier_id = i.supplier_id
       ) as most_recent_order_date
from items i;

1. Это почти именно то, что мне нужно. Как также получить PO #, который также является полем в таблице purchase_info?

2. @L.Levine . . . Если вы используете базу данных, которая поддерживает боковые соединения (иногда вводимые с помощью apply ), то вы должны это сделать. В противном случае вы можете использовать другой коррелированный подзапрос.

Ответ №2:

Вы должны группировать только для K.item_id,PurchaseInfo.po_no (а не для PurchaseInfo.order_date, иначе вы не найдете max() ), и вы также должны использовать DISTINCT вместо group by, если вы не используете функцию объединения

 SELECT 
   K.item_ID, 
   max(PurchaseInfo.order_date) as [Last Purchase Date],
   PurchaseInfo.po_no
FROM 
   Items K
   left outer join ( 
   select distinct order_date, supplier_id, po_no, item_id 
   from Purchases
  ) as  PurchaseInfo
ON  PurchaseInfo.item_id = K.item_id and PurchaseInfo.supplier_id = K.supplier_id
GROUP BY  K.item_ID ,  PurchaseInfo.po_no

Ответ №3:

Этот запрос:

 select
  i.item_id,
  max(p.order_date) [Last Purchase Date]
from items i left join purchases p
on p.item_id = i.item_id
group by i.item_id

возвращает дату последней покупки для каждого элемента.
Если вам также нужен столбец po_no , то присоедините его к purchases :

 select t.*, p.po_no 
from (
  select
    i.item_id,
    max(p.order_date) [Last Purchase Date]
  from items i left join purchases p
  on p.item_id = i.item_id
  group by i.item_id
) t left join purchases p
on p.item_id = t.item_id and p.order_date = t.[Last Purchase Date]