表#a1如下：
- SQL code
id codeid listdate quantity1 123 2012-05-01 102 123 2012-02-02 20 3 456 2012-09-01 303 123 2012-01-03 404 456 2012-10-03 505 789 2012-10-08 60
表#a2如下：
- SQL code
id codeid listdate1 123 2012-05-052 456 2012-09-043 789 2012-09-08 4 098 2012-09-23
我想根据#a2.codeid=#a1.codeid，而且#a2.listdate>#a1.listdate，取出#a1的最近两行记录，并在
#a2表基础上显示出来。
- SQL code
id codeid litdate f1 f31 123 2012-05-05 2012-05-01,2012-02-02 10,202 456 2012-09-04 2012-09-01 303 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语句？
------解决方案--------------------
- SQL code
if OBJECT_ID('A1') is not null drop table A1create table A1(codeid nvarchar(10),listdate datetime,quantity int)insert into A1select '123','2012-05-01',10 union allselect '123','2012-02-02',20 union allselect '456','2012-09-01',30 union allselect '123','2012-01-03',40 union allselect '456','2012-10-03',50 union allselect '789','2012-10-08',60if OBJECT_ID('A2') is not null drop table A2create table A2(codeid nvarchar(10),listdate datetime)insert into A2select '123','2012-05-05' union allselect '456','2012-09-04' union allselect '789','2012-09-08' union allselect '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 f3098 2012-09-23 NULL NULL123 2012-05-05 2012-05-01,2012-02-02 10,20456 2012-09-04 2012-09-01 30789 2012-09-08 NULL NULL*/