比如表#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语句?
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 listdate1FROM #t A
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
?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)
*/
(select max(listdate) from #t B where a.productname = b.productname
and b.listdate < a.listdate) as listdate1FROM #t A