，同一张表查询有关问题
求助，同一张表查询问题
有一张表里面存储着商品不同时期的价格， 结构如下
id，name，price，date
想通过一条一句查询得到 old_price为new_price前一天的价格，如下结构
id，name，old_price,new_price,date
------解决方案--------------------
------解决方案--------------------
有一张表里面存储着商品不同时期的价格， 结构如下
id，name，price，date
想通过一条一句查询得到 old_price为new_price前一天的价格，如下结构
id，name，old_price,new_price,date
------解决方案--------------------
CREATE TABLE #t
(
ID INT,
[Name] VARCHAR(32),
price INT,
date DATETIME
)
INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',10,'2014-08-30')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',20,'2014-08-30')
INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',11,'2014-08-31')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',21,'2014-08-31')
INSERT INTO #t(ID,[Name],price,date) VALUES(1,'aa',9,'2014-09-01')
INSERT INTO #t(ID,[Name],price,date) VALUES(2,'bb',19,'2014-09-01')
SELECT t_newer.ID,t_newer.Name,t_newer.price 'New_price',t_older.price 'Older_price' FROM
(
SELECT * FROM #t WHERE CONVERT(VARCHAR(16),date,23) = CONVERT(VARCHAR(16),GETDATE(),23)
) t_newer INNER JOIN
(SELECT * FROM #t WHERE DATEDIFF(dd,date,GETDATE()) = 1) t_older
ON t_newer.ID = t_older.ID
------解决方案--------------------
select
a.id,.a.name,b.price as old_price,a.price as new_price,a.date
from
(select top 1 * from tb order by date desc) as a
inner join
(select top 1 * from (select top 2 * from tb order by date desc) as t) as b
on
a.date=b.date+1