关于取出最近一次日期的sql语句
比如表#t数据如下：
- SQL code
id productname listdate1 A 2011-01-022 B 2011-01-053 A 2010-02-034 C 2011-03-045 A 2011-04-056 B 2011-01-01
我想取出在#t.productname有相同时，在listdate1里显示出最近一次日期，如下：
- SQL code
id productname listdate listdate11 A 2011-01-02 2010-02-032 B 2011-01-05 2011-01-013 A 2010-02-03 null4 C 2011-03-04 null5 A 2011-04-05 2011-01-026 B 2011-01-01 null
如何写sql语句？
[解决办法]
- SQL code
SELECT * , (select min(listdate) from #t B where a.productname = b.productname and b.listdate > a.listdate) as listdate1FROM #t A
[解决办法]
- SQL code
select n.id.miproductname,n.listdate,m.listdate1 from (select id,productname,listdate listdate1 from test awhere not exists(select 1 from test b where a.productname=b.productname and a.listdate <b.listdate ))tright join test non ty.id=n.id
[解决办法]
？LZ？最近一次的日期是按照什么排序的呢？
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (id int,productname nvarchar(2),listdate datetime)insert into [TB]select 1,'A','2011-01-02' union allselect 2,'B','2011-01-05' union allselect 3,'A','2010-02-03' union allselect 4,'C','2011-03-04' union allselect 5,'A','2011-04-05' union allselect 6,'B','2011-01-01'select * from [TB]SELECT id , productname , listdate , ( SELECT TOP 1 listdate FROM TB WHERE productname = A.Productname AND listdate <> A.listdate AND id >A.id ORDER BY listdate asc ) AS listdate2FROM dbo.TB A/*id productname listdate listdate2----------- ----------- ----------------------- -----------------------1 A 2011-01-02 00:00:00.000 2010-02-03 00:00:00.0002 B 2011-01-05 00:00:00.000 2011-01-01 00:00:00.0003 A 2010-02-03 00:00:00.000 2011-04-05 00:00:00.0004 C 2011-03-04 00:00:00.000 NULL5 A 2011-04-05 00:00:00.000 NULL6 B 2011-01-01 00:00:00.000 NULL(6 row(s) affected)*/
[解决办法]