报表设计
- SQL code
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
[解决办法]
- SQL code
select gsdm,kjnd,dm, (select sum(je) from tb where jhyf<=t.jhyf)from tb twhere 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 TPfrom 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.DMgroup by t.GSDM,t.KJND,t.DM,e.JE
[解决办法]
- SQL code
--sql2005declare @KJND intdeclare @JHYF intset @KJND=2011set @JHYF=2select 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 awhere KJND=@KJND and JHYF=@JHYF
[解决办法]
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (GSDM nvarchar(6),KJND int,JHYF int,DM int,JE int)insert into [TB]select '001',2010,1,110,50 union allselect '001',2010,2,110,30 union allselect '001',2011,1,110,60 union allselect '001',2011,2,110,35select * from [TB]DECLARE @i INT = 2010SELECT 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 TBWHERE KJND = @iGROUP BY GSDM , KJND , DM