我的一个表里面有这样两个栏位Qty 为数量 Sdate 为时间.
现在是想查某个月时分别会出现两个栏位,比如说产生Qty1为要查的某个月的数量,qty2为某个月的前一个月的数量!Qty Sdate
100 2007-05
200 2007-06
500 2007-04
30 2007-07查2007-06时产生如下:
Qty1 Qty2
200 100谢谢!
现在是想查某个月时分别会出现两个栏位,比如说产生Qty1为要查的某个月的数量,qty2为某个月的前一个月的数量!Qty Sdate
100 2007-05
200 2007-06
500 2007-04
30 2007-07查2007-06时产生如下:
Qty1 Qty2
200 100谢谢!
select distinct (case right(Sdate,2)=month(getdate()) then Qty end ) as Qty1,
(case right(Sdate,2)=month(getdate())-1 then Qty end ) as Qty2
from table
set @date='2007-06' --条件select
max(case when sdate=convert(varchar(7),dateadd(month,-1,@date+'-01'),120) then qty end) as qty1,
max(case when sdate=@date then qty end) as qty2
from tablename
Qty int , Sdate nvarchar(10))
insert into aw
select 100, '2007-05'
union select 200, '2007-06'
union select 500, '2007-04'
union select 30 , '2007-07'select distinct max(case when right(Sdate,2)=month('2007-06-01') then Qty end ) as Qty1,
max(case when right(Sdate,2)=month('2007-06-01')-1 then Qty end ) as Qty2
from aw --result
200 100
on datediff(a.Sdate+'-1',b.Sdate+'-1')=1 where a.Sdate='2007-06'
select a.Qty Qty1,b.Qty Qty2 from aw a left join aw b
on datediff(mm,b.Sdate+'-1',a.Sdate+'-1')=1 where a.Sdate='2007-06'200 100
insert @t select 100,'2007-05' union
select 200,'2007-06' union
select 500,'2007-04' union
select 30,'2007-07'
declare @d char(7)
set @d = '2007-06'
select sum(case when sdate = @d then Qty end) as quy1,
sum(case when sdate = convert(char(7),dateadd(mm,1,@d+'-01' ),120) then qty end) as qty2
from @t/*quy1 qty2
----------- -----------
200 30
*/
create table #t(Qty int,Sdate char(7))insert into #t values(100,'2007-05')
insert into #t values(200,'2007-06')
insert into #t values(500,'2007-04')
insert into #t values(30, '2007-07')select a.Qty as Qty1,b.Qty as Qty2 from #t a,#t b
where a.Sdate=convert(char(7),getdate(),120)
and b.Sdate=convert(char(7),dateadd(m,-1,getdate()),120)drop table #t