关于写sql语句的写法

[fstao]

表#a1如下：

id    codeid   listdate        quantity
1       123     2012-05-01       10
2       123     2012-02-02       20 
3       456     2012-09-01       30
3       123     2012-01-03       40
4       456     2012-10-03       50
5       789     2012-10-08       60

表#a2如下：

id     codeid    listdate
1      123       2012-05-05
2      456       2012-09-04
3      789       2012-09-08 
4      098       2012-09-23

我想根据#a2.codeid=#a1.codeid，而且#a2.listdate>#a1.listdate，取出#a1的最近两行记录，并在
#a2表基础上显示出来。

id     codeid    litdate               f1                  f3
1      123       2012-05-05     2012-05-01,2012-02-02     10,20
2      456       2012-09-04     2012-09-01                30
3      789       2012-09-08     
4      098       2012-09-23

id=1的f1='2012-05-01,2012-02-02'原因是#a2.codeid=#a1.codeid和#a2.listdate>#a1.listdate取出最近2行数据，#a1.listdate只有'2012-05-01'和'2012-02-02'；同理f3='10,20'，也是因为#a2.codeid=#a1.codeid和#a2.listdate>#a1.listdate取出最近2行数据，#a1.listdate只有'2012-05-01'和'2012-02-02'，其相应的#a1.quantity是'10'和'20'。
id=2的f1='2012-09-01'的原因是#a2.codeid=#a1.codeid和#a2.listdate>#a1.listdate取出最近2行数据，#a1.listdate只有'2012-09-01'；同理f3='30'。
id=3和id=4的f1和f3为空的原因也是根据是#a2.codeid=#a1.codeid和#a2.listdate>#a1.listdate取出最近2行数据，找不出#a1.listdate和#a1.quantity的数据出来。

请问如何写sql语句？

[ChangeMyself2012]

if OBJECT_ID('A1') is not null drop table A1
create table A1(codeid nvarchar(10),listdate datetime,quantity int)

insert into A1
select '123','2012-05-01',10 union all
select '123','2012-02-02',20 union all
select '456','2012-09-01',30 union all
select '123','2012-01-03',40 union all
select '456','2012-10-03',50 union all
select '789','2012-10-08',60

if OBJECT_ID('A2') is not null drop table A2
create table A2(codeid nvarchar(10),listdate datetime)

insert into A2
select '123','2012-05-05' union all
select '456','2012-09-04' union all
select '789','2012-09-08' union all
select '098','2012-09-23' 


select distinct A2.codeid,convert(nvarchar(10),A2.listdate,120) as listdate,
Stuff((select top 2 ','+convert(nvarchar(10),listdate,120) from A1 where codeid=A2.codeid and listdate<A2.listdate order by listdate desc for XML path('')),1,1,'') as f1,
Stuff((select top 2 ','+convert(nvarchar(10),quantity) from A1 where codeid=A2.codeid and listdate<A2.listdate order by listdate desc for XML path('')),1,1,'') as f3
 from A2  
left join A1 on A1.codeid=A2.codeid 

/*
codeid listdate f1		                f3
098	2012-09-23	NULL	                NULL
123	2012-05-05	2012-05-01,2012-02-02	10,20
456	2012-09-04	2012-09-01	30
789	2012-09-08	NULL	                NULL
*/