关于取出最近一次日期的sql语句

[fstao]

比如表#t数据如下：

id   productname      listdate
1        A              2011-01-02
2        B              2011-01-05
3        A              2010-02-03
4        C              2011-03-04
5        A              2011-04-05
6        B              2011-01-01

我想取出在#t.productname有相同时，在listdate1里显示出最近一次日期，如下：

id   productname      listdate         listdate1
1        A              2011-01-02       2010-02-03
2        B              2011-01-05       2011-01-01
3        A              2010-02-03       null
4        C              2011-03-04       null
5        A              2011-04-05       2011-01-02
6        B              2011-01-01       null

如何写sql语句？

[天-笑]

SELECT * ,
    (select min(listdate) from #t B where a.productname = b.productname 
            and b.listdate > a.listdate) as listdate1

FROM #t A

[Mr_Nice]

id   productname      listdate         listdate1
1        A              2011-01-02       2010-02-03
2        B              2011-01-05       2011-01-01
3        A              2010-02-03       null       --这里不是2011-04-05？
4        C              2011-03-04       null
5        A              2011-04-05       2011-01-02
6        B              2011-01-01       null

[开着拖拉机泡妞]

select n.id.miproductname,n.listdate,m.listdate1 from (
select id,productname,listdate listdate1 from test a
where not exists(select 1 from test b where a.productname=b.productname and a.listdate <b.listdate ))t
right join test n
on ty.id=n.id

[Mr_Nice]

？LZ？最近一次的日期是按照什么排序的呢？

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (id int,productname nvarchar(2),listdate datetime)
insert into [TB]
select 1,'A','2011-01-02' union all
select 2,'B','2011-01-05' union all
select 3,'A','2010-02-03' union all
select 4,'C','2011-03-04' union all
select 5,'A','2011-04-05' union all
select 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 listdate2
FROM    dbo.TB A


/*
id          productname listdate                listdate2
----------- ----------- ----------------------- -----------------------
1           A           2011-01-02 00:00:00.000 2010-02-03 00:00:00.000
2           B           2011-01-05 00:00:00.000 2011-01-01 00:00:00.000
3           A           2010-02-03 00:00:00.000 2011-04-05 00:00:00.000
4           C           2011-03-04 00:00:00.000 NULL
5           A           2011-04-05 00:00:00.000 NULL
6           B           2011-01-01 00:00:00.000 NULL

(6 row(s) affected)
*/

[anzhiqiang_touzi]

[Quote=引用 1 楼 的回复:]
SQL code

SELECT * ,
(select min(listdate) from #t B where a.productname = b.productname
and b.listdate > a.listdate) as listdate1

FROM #t A
[/Quote]

[fstao]

应该是这样写才对，根据上面的语句改了一下：

SELECT * ,
    (select max(listdate) from #t B where a.productname = b.productname 
            and b.listdate < a.listdate) as listdate1

FROM #t A