MYSQL 查询 - 获取价格发生变化的行 [英] MYSQL query - get rows where price is changed
问题描述
我有两个表即.订单和订单项.订单表包含日期,而订单项表包含该订单的数量和价格.我想找到价格发生变化的日期.例如,今天的价格是 10,2 天后变为 11,然后在接下来的 5 天内保持 11,然后在 5 天后再次变为 10,因此结果将是 3 个日期 10、11、10,日期将是价格改变了
I have two tables viz. order and orderitem. Order table contains date and orderitem table has quantity and price for that order. I want to find the dates where the price has been changed. for example today the price is 10 and after 2 days it become 11 and then remain 11 for next 5 days then again it becomes 10 after 5 days so the result would be 3 dates 10, 11, 10 and the date would be when the price changed
Date price
'2015-04-29 14:48:23', 15.99
'2015-04-30 14:20:11', 15.99
'2015-04-30 21:05:11', 15.99
'2015-05-04 01:41:12', 15.99
'2015-07-28 05:52:20', 15.99
'2015-07-29 16:55:48', 15.99
'2015-08-01 00:31:53', 15.99
'2015-08-15 22:41:49', 15.99
'2015-08-17 02:37:43', 15.99
'2015-08-17 13:28:03', 15.99
'2015-08-23 04:16:31', 15.99
'2015-08-27 08:43:48', 15.99
'2015-08-28 18:42:27', 15.99
'2015-08-29 15:47:17', 15.99
'2015-08-30 03:33:15', 15.99
'2015-10-29 03:45:25', 15.99
'2015-10-29 18:24:20', 15.99
'2015-10-30 18:17:18', 15.99
'2015-11-02 15:28:16', 16.99
'2015-11-03 01:59:03', 16.99
'2015-11-14 18:22:39', 16.99
'2015-11-20 02:48:59', 16.99
'2015-12-16 18:59:54', 16.99
'2015-12-28 04:08:22', 16.99
'2016-01-12 03:21:35', 16.99
'2016-01-18 00:43:56', 16.99
'2016-01-18 20:11:23', 16.99
'2016-02-10 19:07:57', 16.99
'2016-02-26 14:24:29', 16.99
'2016-03-28 10:17:24', 16.99
'2016-03-31 23:33:53', 16.99
'2016-04-01 20:03:03', 17.99
'2016-05-31 20:30:50', 15.99
我想获取价格已更改的所有日期.这里输出是
I want to get all those date where the price has been changed. Here the output would be
'2015-04-29 14:48:23', 15.99
'2015-11-02 15:28:16', 16.99
'2016-04-01 20:03:03', 17.99
'2016-05-31 20:30:50', 15.99
即显示价格更改的日期.
i.e. show dates when the price has been changed.
到目前为止,我已经编写了这个 MYSQL 查询,但它提供了所有行并且不过滤结果:
I have written this MYSQL query so far but it gives all rows and does not filter the results:
SELECT date, price FROM orderitem
inner join orders o1 on o1.orderid = orderitem.orderid
where productname = 'IBC'
and price <> (
select price from orderitem
inner join orders on orders.orderid = orderitem.orderid
where orders.date< o1.date
order by date desc
limit 1
)
任何建议将不胜感激.
推荐答案
对于这样的事情,您需要两次通过,因为您要将每一行与之前的行进行比较.
For something like this, you need two passes, because you will be comparing each row to the one before it.
这样做的一种方法是首先:
One such way of doing this would be to first:
SET @lastprice = 0.0;
然后,您的第一次通过"将如下所示:
Then, your "first pass" will look like:
SELECT `Date`, @lastprice AS `oldprice`, @lastprice := `price` AS `newprice`
FROM `tablename` ORDER BY `Date` ASC;
这里的顺序很重要,因为您想建立一个按时间顺序排列的时间表.
The order is important here, as you want to build a chronological timeline.
然后,您需要做的就是从该结果中检索新旧价格不同的行.像这样的事情会做:
Then, all you need to do is retrieve the rows from this result where the old and new prices are different. Something like this will do:
SELECT * FROM ("Above subquery here") `tmp` WHERE `oldprice` != `newprice`;
您的最终查询将如下所示:
Your final query would look something like this:
SET @lastprice = 0.0;
SELECT * FROM (
SELECT `Date`, @lastprice AS `oldprice`, @lastprice := `price` AS `newprice`
FORM `tablename` ORDER BY `Date` ASC
) `tmp`
WHERE `oldprice` != `newprice`;
或者,您可能更喜欢使用临时表来存储子查询结果.在这种情况下,它看起来像这样:
Alternatively, you may prefer to use a temporary table to store the subquery results. In that case, it would look something like this:
SET @lastprice = 0.0;
CREATE TEMPORARY TABLE `tmp`
SELECT `Date`, @lastprice AS `oldprice`, @lastprice := `price` AS `newprice`
FROM `tablename` ORDER BY `Date` ASC;
SELECT * FROM `tmp` WHERE `oldprice` != `newprice`;
DROP TEMPORARY TABLE `tmp`;
具体的实现取决于你,但这将是一个很好的方法.
The exact implementation is up to you, but this would be a good way to do it.
这篇关于MYSQL 查询 - 获取价格发生变化的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!