Как составить SQL запрос

← →
AndrewK © (2002-01-29 09:16) [0]

Добрый день!

Есть две таблицы:

Products Prices

Code int IDR +
Name varchar(50) Product_ID int
Weight float ActualDate datetime
Capacity float Cost float

Поле Prices.Product_ID -> Products.Code

Таблица Prices содержит информацию о динамике цен. Поле ActualDate указывает дату с которой меняется цена на изделие с кодом Product_ID.

Вопрос такой.
Как правильно написать запрос, который позволил бы получить список изделий и цены к ним на заданную дату.

Например: Надо узнать цены на изделия на 29.01.2002

Name Cost ActualDate

Изделие 1 10 10.12.2001
Изделие 2 15 28.01.2002
Изделие 3 20 10.12.2001
Изделие 4 2 10.12.2001
Изделие 5 100 29.01.2002

В поле ActualDate выбирается дата, которая максимальная из меньших или равных заданной контрольной точке, здесь 29.01.2002.

Заранее благодарен.

← →
Gregory (2002-01-29 09:40) [1]

@p_date - Дата, на которую надо выдать цены

SELECT Name, Cost, ActualDate
FROM Products
INNER JOIN Prices t1 ON t1.Product_ID = Code
WHERE NOT( EXISTS ( SELECT 1
FROM Prices t2
WHERE t2.Product_ID = Code
AND t2.ActualDate > t1.ActualDate
AND t1.ActualDate <= @p_date )
AND t1.ActualDate <= @p_date

Это запрос не выдаст в списке товар у которого на указанную дату цена отсутствует. Если надо выдавать, но с NULL-ценой, то можно заменить на.

SELECT Name, Cost, ActualDate
FROM Products
LEFT OUTER JOIN Prices t1 ON t1.Product_ID = Code
WHERE NOT( EXISTS ( SELECT 1
FROM Prices t2
WHERE t2.Product_ID = Code
AND t2.ActualDate > t1.ActualDate
AND t1.ActualDate <= @p_date )
AND ( t1.ActualDate <= @p_date OR
t1.ActualDate IS NULL )

← →
amamed_3071 (2002-01-29 10:24) [2]

Select P.CODE,P.NAME,PRICE.ACTUALDATE,PRICE.COST from Products P
left outer join
(SELECT Product_ID,COST, ActualDate FROM PRICES PR
INNER JOIN
(Select Product_ID,MAX(ActualDate) MD FROM PRICES P
WHERE ActualDate<=@DATE GROUP BY Product_ID) PRMAX
ON PRMAX.MD=PR.ActualDate AND PR.Product_ID=PRMAX.Product_ID
WHERE PR.ActualDate<=@DATE
) PRICE ON P.CODE=PRICE.Product_ID

...ORDER BY...

← →
SergSuper (2002-01-29 11:22) [3]

Как то всё сложно, по-моему так проще
select Name, Cost, ActualDate
from Products o, Prices i
where i.Product_ID = o.Code
and i.ActualDate=(select max(ActualDate) from Prices i2 where i2.Product_ID = o.Code and i2.ActualDate<=@date)
но такой запрос может работать медленно(при больших объёмах данных)
Для ускорения можно сначала выбрать нужные даты по товарам во временную таблицу и потом уже выбирать цены с её помощью. Так мы уйдём от вложенных запросов. Но всё равно не очень быстро.
Можно еще упростить запрос если несколько усложнить структуру таблицы цен - добавить поле с датой следующей цены. Для последней даты будет ставиться заведомо большая дата. Т.е. примерно так:
Name Cost ActualDate NextDate Изделие 1 10 10.12.2001 31.10.2065 Изделие 2 15 01.01.2002 28.01.2002 Изделие 2 16 28.01.2002 31.10.2065 Изделие 3 20 10.12.2001 31.10.2065
Тогда запрос еще упрощается и соответственно увеличивается скороть выполнения:
select Name, Cost, ActualDate
from Products o, Prices i
where i.Product_ID = o.Code
and i.ActualDate<=@date and i.NextDate>=@date
Но усложняется ведение таблицы цен - за всё надо платить.

Огромное спасибо всем

Как составить SQL запрос Найти похожие ветки