选择值已更改的行

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

问题最初被标记为SQL-SERVER(没有版本)。这适用于SQL 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;

将每行的值与前一天的值进行比较:

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))
)