求报表分析- -解决思路

求报表分析- -

SQL code

有TAB如下：KJND    GSDM    YSDWDM    JE    SH_RQ       DM2011    888     001       1000  20110101    06012011    888     001       1000  20110201    06012011    888     001       1000  20110201    05012011    888     001       1000  20110101    04012011    888     002       1000  20110101    06012011    888     002       1000  20110201    04012011    888     003       1000  20110101    06012011    888     003       1000  20110201    05012011    888     003       1000  20110201    0401................................................需求：取其中001、002、003这3个单位，如查询SH_RQ为2011年2月的数据KJND GSDM YSDWDM M1(DM为:0401/0501/0601) L1(DM为:0401/0501/0601) M2(DM为:0601) L2(DM为:0601) M3(DM为:0401/0501) L3(DM为:0401/0501)2011  888    001    2000    4000    1000  2000   1000  20002011  888    002    1000    2000    0     1000   1000  10002011  888    003    2000    3000    0     1000   2000  2000

SQL code

select  kjnd,  gsdm,  ysdmdm,  sum(case when dm in('0401','0501','0601') then je else 0 end) as m1,  sum(case when dm='0601') then je else 0 end) as m1,...from   tbgorup by     kjnd,  gsdm,  ysdmdm
[解决办法]
參照例子
http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html
[解决办法]
把你后面的l1,d1之类的数据解释一下怎么来的?
[解决办法]
SQL code
累计合计这样计算select *,(select sum(m1) from tb where SH_RQ<=t.SH_RQ) from tb t
[解决办法]
不知道楼主要统计什么,供参考吧:
SQL code
select KJND, GSDM, YSDWDM,M1=sum(case when dm in('0601','0501','0601') then JE else 0 end),L1=sum(case when dm in('0601','0501','0601') then .... else 0 end),M2=sum(...from tabgroup by KJND, GSDM, YSDWDM
[解决办法]
SQL code
create table tb(KJND int, GSDM int, YSDWDM varchar(10), JE int, SH_RQ datetime, DM varchar(10))insert into tb values(2011 , 888 , '001' , 1000 ,'20110101' , '0601')insert into tb values(2011 , 888 , '001' , 1000 ,'20110201' , '0601')insert into tb values(2011 , 888 , '001' , 1000 ,'20110201' , '0501')insert into tb values(2011 , 888 , '001' , 1000 ,'20110101' , '0401')insert into tb values(2011 , 888 , '002' , 1000 ,'20110101' , '0601')insert into tb values(2011 , 888 , '002' , 1000 ,'20110201' , '0401')insert into tb values(2011 , 888 , '003' , 1000 ,'20110101' , '0601')insert into tb values(2011 , 888 , '003' , 1000 ,'20110201' , '0501')insert into tb values(2011 , 888 , '003' , 1000 ,'20110201' , '0401')goselect t.KJND , t.GSDM , t.YSDWDM,       M1 = (select sum(je) from tb where KJND = t.KJND and YSDWDM = t.YSDWDM and datepart(mm,sh_rq) = 2),       L1 = (select sum(je) from tb where KJND = t.KJND and YSDWDM = t.YSDWDM)from tb t where  YSDWDM in ('001','002','003') and datepart(mm,sh_rq) = 2group by t.KJND , t.GSDM , t.YSDWDMdrop table tb/*KJND        GSDM        YSDWDM     M1          L1          ----------- ----------- ---------- ----------- ----------- 2011        888         001        2000        40002011        888         002        1000        20002011        888         003        2000        3000（所影响的行数为 3 行）*/ 
 
[解决办法]
SQL code
use Tempdbgo--> -->  if not object_id(N'Tempdb..#TAB') is null    drop table #TABGoCreate table #TAB([KJND] int,[GSDM] int,[YSDWDM] nvarchar(3),[JE] int,[SH_RQ] Datetime,[DM] nvarchar(4))Insert #TABselect '2011',888,N'001',1000,'20110101',N'0601' union allselect '2011',888,N'001',1000,'20110201',N'0601' union allselect '2011',888,N'001',1000,'20110201',N'0501' union allselect '2011',888,N'001',1000,'20110101',N'0401' union allselect '2011',888,N'002',1000,'20110101',N'0601' union allselect '2011',888,N'002',1000,'20110201',N'0401' union allselect '2011',888,N'003',1000,'20110101',N'0601' union allselect '2011',888,N'003',1000,'20110201',N'0501' union allselect '2011',888,N'003',1000,'20110201',N'0401'GoSELECT     a.[KJND],a.[GSDM],a.[YSDWDM],a.[M1],    [L1]=SUM(CASE WHEN b.DM in('0401','0501','0601') THEN b.[JE] ELSE 0 END),    a.[M2],    [L2]=SUM(CASE WHEN b.DM in('0601') THEN b.[JE] ELSE 0 END),    a.[M3],    [L1]=SUM(CASE WHEN b.DM in('0401','0501') THEN b.[JE] ELSE 0 END)    FROM (Select     [KJND],    [GSDM],    [YSDWDM],    [SH_RQ],    [M1]=SUM(CASE WHEN DM in('0401','0501','0601') THEN [JE] ELSE 0 END),     [M2]=SUM(CASE WHEN DM IN('0601') THEN [JE] ELSE 0 end),    [M3]=SUM(CASE WHEN DM IN('0401','0501') THEN [JE] ELSE 0 end)from #TAB  AS aWHERE a.[SH_RQ]='20110201'GROUP BY [KJND],[GSDM],[YSDWDM],[SH_RQ])a,#TAB AS bWHERE a.[KJND]=b.[KJND] AND a.[GSDM]=b.[GSDM] AND a.[YSDWDM]=b.[YSDWDM] AND b.[SH_RQ]<=a.[SH_RQ]GROUP BY a.[KJND],a.[GSDM],a.[YSDWDM],a.[M1],a.[M2],a.[M3],a.[SH_RQ]/*KJND    GSDM    YSDWDM    M1    L1    M2    L2    M3    L12011    888    001    2000    4000    1000    2000    1000    20002011    888    002    1000    2000    0    1000    1000    10002011    888    003    2000    3000    0    1000    2000    2000*/
[解决办法]
探讨

补充：
M1：本月合计 ZBLYDM 包括（0401、0501、0601）
L1：累计合计 ZBLYDM 包括（0401、0501、0601）
M2：本月合计 ZBLYDM 包括（0601）
L2：累计合计 ZBLYDM 包括（0601）
M3：本月合计 ZBLYDM 包括（0401、0501）
L3：累计合计 ZBLYDM 包括（0401、0501）
也就是M1=M2+M3
  L1=L2+L3
一共3中指标来源，后几列是拆分出来的。

[解决办法]
探讨

SQL code
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#TAB') is null
    drop table #TAB
Go
Create table #TAB([KJND] int,[GSDM] int,[YSDWDM] nvarchar(3),[JE] int,[SH_RQ] Datetime,[DM] n……