/*
    查询所有的时候，将产品的上一个月也查询出来
    cinvname     上一个月      这个月
    产品1        100         200
    产品2        100        200
*/
declare @table table
(
    cinvname varchar(255),
    cinvdate datetime,
    cinvprice float
)
insert into @table  
select '产品1',cast('2011-5-6' as datetime),100 union all
select '产品1',cast('2011-6-6' as datetime),200 union all
select '产品2',cast('2012-5-6' as datetime),100 union all
select '产品2',cast('2012-6-6' as datetime),200

select * from @table
// 单个产品搜索的话 可以dateadd(M,-1,cinvdate)这样，如果查询全部的呢？
// 是否需要用游标先查出所有上个月产品的价格保存到临时表？

select t1.cinvname,t2.cinvprice 上一個月,t1.cinvprice 本月
from @table t1--本月
left join @table t2 on t1.cinvname=t2.cinvname and t1.cinvdate=dateadd(M,1,t2.cinvdate)
where datediff(m,t1.cinvdate,getdate())=0;
--getdate()你的時間，不知是不是這個意思

------解决方案--------------------
/*
   查询所有的时候，将产品的上一个月也查询出来
   cinvname     上一个月      这个月
   产品1        100         200
   产品2        100        200
*/
declare @table table
(
   cinvname varchar(255),
   cinvdate datetime,
   cinvprice float
)
insert into @table 
select '产品1',cast('2011-5-6' as datetime),100 union all
select '产品1',cast('2011-6-6' as datetime),200 union all
select '产品2',cast('2012-5-6' as datetime),100 union all
select '产品2',cast('2012-6-6' as datetime),200

select  
	t1.cinvname,
	t2.cinvprice 上一個月,
	t1.cinvprice 本月
from @table t1--本月
left join @table t2  
on t1.cinvname=t2.cinvname  
and t1.cinvdate=dateadd(M,1,t2.cinvdate)
where t2.cinvprice is not null


-----------------------------------------------
cinvname 上一个月 这个月

产品1	100	200
产品2	100	200

------解决方案--------------------

SQL code
declare @table table
(
    cinvname nvarchar(255),
    cinvdate datetime,
    cinvprice float
)
insert into @table  
select N'产品1',cast('2012-7-6' as datetime),100 union all
select N'产品1',cast('2012-8-6' as datetime),200 union all
select N'产品2',cast('2012-7-6' as datetime),100 union all
select N'产品2',cast('2012-8-6' as datetime),200

select * from @table

select t.[cinvname],sum(isnull(t.[上个月],0)) '上个月',sum(isnull(t.[这个月],0)) '这个月'
from
(
    select [cinvname],sum(isnull(case when convert(varchar(7),[cinvdate],120)=convert(varchar(7),dateadd(month,-1,getdate()),120) then [cinvprice] end,0)) '上个月',sum(isnull(case when convert(varchar(7),[cinvdate],120)=convert(varchar(7),getdate(),120) then [cinvprice] end,0)) '这个月' from @table
    where convert(varchar(7),[cinvdate],120)=convert(varchar(7),getdate(),120) or convert(varchar(7),[cinvdate],120)=convert(varchar(7),dateadd(month,-1,getdate()),120) 
    group by [cinvname],convert(varchar(7),[cinvdate],120)
) t
group by t.[cinvname]
/*

(4 row(s) affected)
cinvname                                                                                                                                                                                                                                                        cinvdate                cinvprice
--------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------
产品1                                                                                                                                                                                                                                                             2012-07-06 00:00:00.000 100
产品1