- SQL code
/* 查询所有的时候，将产品的上一个月也查询出来 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 allselect '产品1',cast('2011-6-6' as datetime),200 union allselect '产品2',cast('2012-5-6' as datetime),100 union allselect '产品2',cast('2012-6-6' as datetime),200select * from @table// 单个产品搜索的话 可以dateadd(M,-1,cinvdate)这样，如果查询全部的呢？// 是否需要用游标先查出所有上个月产品的价格保存到临时表？小菜！没分了！
------解决方案--------------------
- SQL code
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 allselect N'产品1',cast('2012-8-6' as datetime),200 union allselect N'产品2',cast('2012-7-6' as datetime),100 union allselect N'产品2',cast('2012-8-6' as datetime),200select * from @tableselect 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)) tgroup by t.[cinvname]/*(4 row(s) affected)cinvname cinvdate cinvprice--------------------------------------------------------------------------------------------------------------- ----------------------- ----------------------产品1 2012-07-06 00:00:00.000 100产品1 2012-08-06 00:00:00.000 200产品2 2012-07-06 00:00:00.000 100产品2 2012-08-06 00:00:00.000 200(4 row(s) affected)cinvname 上个月 这个月--------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------产品1 100 200产品2 100 200(2 row(s) affected)*/