sql-server - 及时返回以查看 SQL Server 中是否存在值

Question

我需要在 SQL 中创建一个查询，该查询将采用记录集（select 语句）并返回发生的价格变化的最后一个值。所以像这种情况。

--Create a TableVariable(only in memory)
Declare @CarPrices Table (testid int, car nvarchar(50),price 
nvarchar(50),PricingDate date)
--Insert Data
insert into @CarPrices (testid, car, price, PricingDate) values (1, 'Ford', 
'1.00043', '05/15/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (2, 
'Chevy','1.00043', '05/15/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (3, 'Chevy', 
NULL, '05/16/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (4, 'Ford', 
NULL, '05/16/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (5, 'Ford', 
'1.0053', '05/17/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (6, 'Chevy', 
NULL, '05/17/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (7, 'Chevy', 
NULL, '05/18/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (8, 'Ford', 
NULL, '05/18/2018')
insert into @CarPrices (testid, car, price, PricingDate) values (9, 'Audi', 
'10.0003', '05/18/2018')

目标：查看数据集，获取最新的汽车日期和价格

任何想法或任何尝试。我没有足够小的数据集来使用，所以我使用的是汽车、定价和日期，这本质上是我将在更大范围内做的事情。有什么想法吗？也许是 IIF 语句的复杂案例语句？我真的不知道从哪里开始。

标签： sql-servertsql

Answer 1

对同一辆车的先前非空价格使用外部应用可以解决问题

declare @T table (id int identity(1,1) primary key, Car varchar(30), [day] date, price money);

insert into @T (Car, [day], price) values
('ford', '2018-05-13',5003.00),
('Chevy','2018-05-13',8003.00),
('ford', '2018-05-14',5004.00),
('Chevy','2018-05-14',null),
('ford', '2018-05-15',null),
('Chevy','2018-05-15',8005.00);

select Car, [day], price, coalesce(price, prevPrice) as CorrectedPrice
from @T t1
outer apply 
(
  select top 1 price as prevPrice 
  from @T t2 
  where t2.Car = t1.Car
  and t2.[day] < t1.[day]
  and datediff(day,t2.[day],t1.[day]) <= 5 -- only the previous 5 days
  and t1.price is null 
  and t2.price is not null 
  order by t2.[day] desc
) q1
order by t1.Car, t1.[day];

结果：

Car    day         price    CorrectedPrice
-----  ----------  -------  --------------
Chevy  2018-05-13  8003,00  8003,00
Chevy  2018-05-14  NULL     8003,00
Chevy  2018-05-15  8005,00  8005,00
ford   2018-05-13  5003,00  5003,00
ford   2018-05-14  5004,00  5004,00
ford   2018-05-15  NULL     5004,00