SQL 关于日期的查询，求个思路

Question

/*
查询所有的时候，将产品的上一个月也查询出来
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),200select * from @table
// 单个产品搜索的话 可以dateadd(M,-1,cinvdate)这样，如果查询全部的呢？
// 是否需要用游标先查出所有上个月产品的价格保存到临时表？ 小菜！没分了！

Answer 1

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()你的時間，不知是不是這個意思

Answer 2

/*
    查询所有的时候，将产品的上一个月也查询出来
    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),200select 
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

Answer 3

/*
    查询所有的时候，将产品的上一个月也查询出来
    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),200select 
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

Answer 4

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),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)
) t
group 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)
*/