GSDM    KJND    JHYF    DM    JE        
001    2010    1    110    50        
001    2010    2    110    30        
001    2011    1    110    60        
001    2011    2    110    35        
…                        
以2011年1月查询                        
GSDM    KJND    DM    本年累计    本月发生    上年本月    同比
001    2011    110    60    60    50    1.2
                        
以2011年2月查询                        
GSDM    KJND    DM    本年累计    本月发生    上年本月    同比
001    2011    110    95    35    30    1.166666667

select
  gsdm,kjnd,dm,
  (select sum(je) from tb where jhyf<=t.jhyf)
from
  tb t
where
  KJND=2011 and JHYF=1

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

SQL code

--GSDM    KJND    JHYF    DM    JE
;with ach as
(
    select GSDM,KJND,JHYF,DM,sum(JE) as JE
    from tb
    group by GSDM,KJND,JHYF,DM
)

select t.GSDM,t.KJND,t.DM,
    (select sum(JE) from ach where GSDM=t.GSDM and KJND=t.KJND and DM=t.DM) YJE,
    sum(t.JE) as MJE,e.JE as LMJE,sum(t.JE)*1./e.JE as TP
from tb t left join ach e 
    on t.GSDM = e.GSDM and t.KJND = e.KJND+1 and t.JHYF = e.JHYF and t.DM = e.DM
group by t.GSDM,t.KJND,t.DM,e.JE

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

SQL code

--sql2005
declare @KJND int
declare @JHYF int
set @KJND=2011
set @JHYF=2
select GSDM,KJND,DM,本年累计,本月发生,上月发生,cast(本月发生 as float)/cast(上月发生 as float) 同比
from
(
select*,sum(je) over(partition by KJND) 本年累计,sum(je) over (partition by KJND,JHYF) 本月发生,
sum(case when KJND=@KJND-1 then je else 0 end ) over (partition by JHYF) 上月发生
from t1
) as a
where KJND=@KJND and JHYF=@JHYF

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

SQL code
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (GSDM nvarchar(6),KJND int,JHYF int,DM int,JE int)
insert into [TB]
select '001',2010,1,110,50 union all
select '001',2010,2,110,30 union all
select '001',2011,1,110,60 union all
select '001',2011,2,110,35

select * from [TB]

DECLARE @i INT = 2010

SELECT  GSDM ,
        KJND ,
        DM ,
        [本年累计] = SUM(JE) ,
        [本月发生] = SUM(CASE WHEN JHYF = DATEPART(mm, GETDATE()) THEN JE
                          ELSE 0
                     END) ,
        [上月发生] = SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1
                               AND KJND = @i THEN JE
                          ELSE 0
                     END) ,
        [同比] = CONVERT(FLOAT, SUM(CASE WHEN JHYF = DATEPART(mm, GETDATE())
                                       THEN JE
                                       ELSE 0
                                  END) * 1.0
        / CASE WHEN SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1
                                  AND KJND = @i THEN JE
                             ELSE 0
                        END) = 0 THEN 1
               ELSE SUM(CASE WHEN DATEDIFF(mm, JHYF, GETDATE()) = 1
                                  AND KJND = @i THEN JE
                             ELSE 0
                        END)
          END)
FROM    TB
WHERE   KJND = @i
GROUP BY GSDM ,
        KJND ,
        DM