如何在没有初始日期和结束日期的情况下查询历史价格？(How to query historical prices where there are no initial dates nor end da

如何在没有初始日期和结束日期的情况下查询历史价格？(How to query historical prices where there are no initial dates nor end dates?)

我试图做一个SQL查询来找到已经筹集最少资金的产品，但是我面临一个问题，那就是我有一个表格跟踪价格的不同日期，例如Product 1在2011-01-01和价格更改的下一个日期之间购买的所有收货人的价格为5000，另一方面，产品1的最新价格日期为2015-01- 01，它的价格是5600，所以这意味着从2015-01-01购买到实际日期的任何收件人的价格为5600。

这是当前问题的SQL小提琴：

http://sqlfiddle.com/#!9/62ec25/9

我所做的查询可以得到答案的答案：

select pr.productName, sum(d.quantity*p.price) from details d, product pr, price p, recipts r where d.Product_ref=pr.ref and pr.ref=p.Product_ref and r.numRecipt=d.Recipts_numRecipt group by pr.productName order by 2 asc;

我非常确定查询是错误的，因为我没有考虑到每个产品的历史价格，但是我找不到使用“之间”SQL运算符的方式，我怎样才能得到预期的结果？

I'm trying to make a SQL query to find the product that has raised the least amount of money, but I'm facing a problem, and it is that I have a table which keeps track of different dates for prices, for example Product 1 has a price of 5000 for all the recipts purchased between 2011-01-01 and the next date of a price change and that is 2012-01-01, on the other hand Product 1 has the latest price date as 2015-01-01 and its price is 5600, so it means that any recipt purchased from 2015-01-01 to an actual date has a price of 5600.

Here is a SQL Fiddle of the current problem:

http://sqlfiddle.com/#!9/62ec25/9

And the query I did to get an aproximation to the answer:

select pr.productName, sum(d.quantity*p.price) from details d, product pr, price p, recipts r where d.Product_ref=pr.ref and pr.ref=p.Product_ref and r.numRecipt=d.Recipts_numRecipt group by pr.productName order by 2 asc;

I'm pretty sure the query is wrong because I'm not taking into consideration the historical price for each product, but I cannot find a way to make use of the "in between" SQL operator, How can I get the expected result?

最满意答案

我想你会想把你的price表变成一个看起来像这样的结构：

然后构建一个查询，该查询在购买日期介于价格日期范围之间的表上加入：

SELECT pr.productName, SUM(d.quantity * p.price) FROM details d INNER JOIN product pr ON d.product_ref = pr.ref INNER JOIN ( SELECT p.product_ref, p.price, MIN(p.priceDate) AS StartDate, MIN(p1.priceDate) AS EndDate FROM price p LEFT JOIN price p1 ON p.product_ref = p1.product_ref WHERE p.priceDate < p1.priceDate GROUP BY p.product_ref, p.price ) p ON pr.ref = p.product_ref INNER JOIN recipts r ON r.numRecipt = d.recipts_numRecipt WHERE r.dateOfPurchase >= p.startDate AND r.dateOfPurchase < p.endDate GROUP BY pr.productName

I think you'd want to get your price table into a structure that looks like:

Then build a query that joins on that table where the purchase date is between the price date range:

SELECT pr.productName, SUM(d.quantity * p.price) FROM details d INNER JOIN product pr ON d.product_ref = pr.ref INNER JOIN ( SELECT p.product_ref, p.price, MIN(p.priceDate) AS StartDate, MIN(p1.priceDate) AS EndDate FROM price p LEFT JOIN price p1 ON p.product_ref = p1.product_ref WHERE p.priceDate < p1.priceDate GROUP BY p.product_ref, p.price ) p ON pr.ref = p.product_ref INNER JOIN recipts r ON r.numRecipt = d.recipts_numRecipt WHERE r.dateOfPurchase >= p.startDate AND r.dateOfPurchase < p.endDate GROUP BY pr.productName

更多推荐