表 AID NAME PRICE DATE1 aaa 500 2009-01-022 aaa 550 2009-02-023 aaa 480 2009-02-054 bbb 800 2008-12-015 bbb 860 2009-02-016 ccc 900 2009-02-057 ddd 1200 2009-01-018 ddd 1500 2009-03-08 想实现1.取每个类别的Name的最新的日期的记录2. 取每个类别Name 的临近的上期记录 进行比较。 当前的记录跟上期记录的价格 是升了还是降了结果如下：NAME PRICE DATEaaa 480 2009-02-05 下降70bbb 860 2009-02-01 上涨60ccc 900 2009-02-05 无ddd 1500 2009-03-08 上涨300请问如何通过语句实现 谢谢===============================================================================declare @t table(ID int, NAME varchar(10), PRICE int,DATE datetime)insert @t select 1,'aaa',500,'2009-1-2'insert @t select 2,'aaa',550,'2009-2-2'insert @t select 3,'aaa',480,'2009-2-5'insert @t select 4,'bbb',800,'2008-12-1'insert @t select 5,'bbb',860,'2009-2-1'insert @t select 6,'ccc',900,'2009-2-5'insert @t select 7,'ddd',1200,'2009-1-1'insert @t select 8,'ddd',1500,'2009-3-8'select a.NAME,a.PRICE,a.DATE,价格变化=case when a.price-b.price<0 then '下降'+ltrim(abs(a.price-b.price)) when a.price-b.price=0 then '无' else '上涨'+ltrim(abs(a.price-b.price)) endfrom (select * from @t t where id =(select top 1 id from @t where name=t.name order by date desc)) a join (select * from @t t where id =(select top 1 id from (select top 2 * from @t where name=t.name order by date desc) t1 where t1.name=t.name order by date)) b on a.name=b.name
