34,590
社区成员
发帖
与我相关
我的任务
分享
/*
查询所有的时候,将产品的上一个月也查询出来
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)这样,如果查询全部的呢?
// 是否需要用游标先查出所有上个月产品的价格保存到临时表?
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 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)
*/
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()你的時間,不知是不是這個意思