我有一张每天显示产品价格的表格。一个产品有三个价格:批发,数量和清关。
该表每天都根据当前价格进行更新,大多数日子不会发生任何变化。但是,我想选择其中一个值发生变化的行,而我正在为这些想法而奋斗。我试过一组,但由于价格上下浮动,这是行不通的。
select date_entered,
WholeSale,
Volume,
Clearance
FROM pricetable
where product = 'TANGO'
order by date_entered desc
上面的选项是从表中为一个集合产品选择所有行。
我如何才能使它只行的变化,批发或数量或清关已发生。
任何帮助都很感激。
发布于 2018-11-19 16:48:49
select p1.date_entered,
p1.WholeSale,
p1.Volume,
p1.Clearance
FROM pricetable p1
CROSS APPLY
--cross apply to most recent prior record
(SELECT TOP 1 *
FROM pricetable p2
where p1.product = p2.product
and p2.date_entered < p1.date_entered
order by p2.date_entered desc) CA
where p1.product = 'TANGO'
and (p1.wholesale != CA.wholesale or p1.volume != CA.volume or p1.clearence != CA.clearence)
order by p1.date_entered desc
发布于 2018-11-19 16:36:34
这个问题最初被标记为SQL-SERVER (没有版本)。这适用于Server 2012+:
使用lag()
。就像这样:
select pt.*
from (select pt.*,
lag(wholesale) over (partition by product
order by date_entered) as prev_wholesale,
lag(volume) over (partition by product
order by date_entered) as prev_volume,
lag(clearance) over (partition by clearance
order by date_entered) as prev_clearance
from pricetable
where product = 'TANGO'
) pt
where (prev_wholesale is null or prev_wholesale <> wholesale) or
(prev_volume is null or prev_volume <> volume) or
(prev_clearance is null or prev_clearance <> clearance)
order by date_entered desc;
发布于 2018-11-19 17:00:59
将每一行的值与前一天的值进行比较:
SELECT * FROM pricetable p WHERE (p.product = 'TANGO') AND
(
(p.WholeSale <> (SELECT WholeSale FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Volume <> (SELECT Volume FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
OR
(p.Clearance <> (SELECT Clearance FROM pricetable WHERE product = 'TANGO' AND date_entered = dateadd(day, -1, p.date_entered))
)
https://stackoverflow.com/questions/53379016
复制相似问题