关于写sql话语的写法

关于写sql语句的写法
表#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*/ 

 

查看更多 下一篇