日期:2014-05-17  浏览次数:20938 次

关于写sql语句的写法
表#a1如下:
SQL code

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如下:
SQL code

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表基础上显示出来。
SQL code

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语句?

------解决方案--------------------
SQL code

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
*/