SQL语句

[叔到用时方恨嫂]

表如下
Variety Suppliers Price Current_month
1    aaa   0.22 2012-4-6
2    bbb   0.53 2012-4-5
3    ccc   0.91 2012-4-13
1    aaa   0.43 2012-5-5
2    bbb   0.32 2012-5-7
想要的结果如下：
Variety Suppliers Price Current_month  last_price
1    aaa   0.43 2012-5-5   0.22
2    bbb   0.32 2012-5-7   0.53
last_price取的是上个月的价格

Variety和Suppliers 是主键,Current_month是datetime型,数据库格式是'2012/5/3'

[Felixzhaowenzhong]

create table tb(Variety int,Suppliers varchar(12),Price money,Current_month datetime)
insert tb 
select 1,'aaa',0.22,'2012/4/6' union 
select 2,'bbb',0.53,'2012/4/5' union 
select 3,'ccc',0.91,'2012/4/13' union 
select 1,'aaa',0.43,'2012/5/5' union 
select 2,'bbb',0.32,'2012/5/7' 

select a.Variety,a.Suppliers,a.Price,a.Current_month,b.price as last_price
from tb a join tb b on a.Variety=b.Variety and a.Suppliers=b.Suppliers and a.Current_month>b.Current_month 
/*
Variety	Suppliers	Price	Current_month	last_price
1	aaa	0.43	2012-05-05	0.22
2	bbb	0.32	2012-05-07	0.53
*/

drop table tb

[寂小魔]

[Quote=引用 1 楼 的回复:]

SQL code
create table tb(Variety int,Suppliers varchar(12),Price money,Current_month datetime)
insert tb
select 1,'aaa',0.22,'2012/4/6' union
select 2,'bbb',0.53,'2012/4/5' union
select 3,'ccc',0……
[/Quote]
+1

[人生无悔]

应该还需改一点才可以，呵呵。。。

[zczhangchao2003]

select a.Variety,a.Suppliers,a.Price,a.Current_month,b.price as last_price
from tb a join tb b on a.Variety=b.Variety and/* a.Suppliers=b.Suppliers and */a.Current_month>b.Current_month 

为什么要考虑注释部分 ？

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test](
[Variety] int,
[Suppliers] varchar(3),
[Price] numeric(3,2),
[Current_month] datetime
)
insert [test]
select 1,'aaa',0.22,'2012-4-6' union all
select 2,'bbb',0.53,'2012-4-5' union all
select 3,'ccc',0.91,'2012-4-13' union all
select 1,'aaa',0.43,'2012-5-5' union all
select 2,'bbb',0.32,'2012-5-7'

select 
    [Variety],[Suppliers],
    [Price],[Current_month],
    (select [Price] from test b
        where a.Variety=b.Variety 
           and month(a.Current_month)=MONTH(b.Current_month)+1)
    as last_price
from 
    test a
where 
    DATEPART(MM,[Current_month])=DATEPART(MM,GETDATE())
/*
Variety	Suppliers	Price	Current_month	last_price
1	aaa	0.43	2012-05-05 00:00:00.000	0.22
2	bbb	0.32	2012-05-07 00:00:00.000	0.53
*/

[人生无悔]

还不对，还要改，呵呵。。。