树形数据汇总查询 - -解决方法

--SQL2000:--查询的数据语句：SELECT DISTINCT B.KJND,B.GSDM,A.FZDM,A.FZMC,ZBZE1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZBZE1 ELSE 0 END),ZBZE2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZBZE2 ELSE 0 END),ZBZE3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZBZE3 ELSE 0 END),--ZBZE4=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZBZE2 ELSE 0 END),--z指标来源没确定JP1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN JP1 ELSE 0 END),JP2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN JP2 ELSE 0 END),JP3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN JP3 ELSE 0 END),LH1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN LH1 ELSE 0 END),LH2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN LH2 ELSE 0 END),LH3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN LH3 ELSE 0 END),CH1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CH1 ELSE 0 END),CH2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CH2 ELSE 0 END),CH3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CH3 ELSE 0 END),HJ1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN HJ1 ELSE 0 END),HJ2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN HJ2 ELSE 0 END),HJ3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN HJ3 ELSE 0 END),CY1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CY1 ELSE 0 END),CY2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CY2 ELSE 0 END),CY3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CY3 ELSE 0 END),CN1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CN1 ELSE 0 END),CN2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CN2 ELSE 0 END),CN3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN CN3 ELSE 0 END),NA1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN NA1 ELSE 0 END),NA2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN NA2 ELSE 0 END),NA3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN NA3 ELSE 0 END),ZS1=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZS1 ELSE 0 END),ZS2=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZS2 ELSE 0 END),ZS3=SUM(CASE WHEN A.FZDM=B.YSKMDM THEN ZS3 ELSE 0 END)FROM(select distinct fzdm,case when len(fzdm)=3 then fzmc when len(fzdm)=5 then '  '+fzmcwhen len(fzdm)=7 then '    '+fzmc end fzmc from gl_fzxzl WHERE FZDM LIKE '2%') AS A,(Select     KJND,    GSDM,    YSKMDM,    ZBZE1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') THEN JE ELSE 0 END), --没有合计ZBZE4的金额    ZBZE2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end),    ZBZE3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') THEN JE ELSE 0 end),    --ZBZE4=SUM(CASE WHEN (ZBLYDM like --未确定') THEN JE ELSE 0 end),--没有确定指标来源    JP1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117001' THEN JE ELSE 0 END),    JP2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117001' THEN JE ELSE 0 end),    JP3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117001' THEN JE ELSE 0 end),    LH1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117002' THEN JE ELSE 0 END),    LH2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117002' THEN JE ELSE 0 end),    LH3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117002' THEN JE ELSE 0 end),    CH1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117004' THEN JE ELSE 0 END),    CH2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117004' THEN JE ELSE 0 end),    CH3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117004' THEN JE ELSE 0 end),    HJ1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117003' THEN JE ELSE 0 END),    HJ2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117003' THEN JE ELSE 0 end),    HJ3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117003' THEN JE ELSE 0 end),    CY1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117005' THEN JE ELSE 0 END),    CY2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117005' THEN JE ELSE 0 end),    CY3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117005' THEN JE ELSE 0 end),    CN1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117006' THEN JE ELSE 0 END),    CN2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117006' THEN JE ELSE 0 end),    CN3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117006' THEN JE ELSE 0 end),    NA1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND YSDWDM='117007' THEN JE ELSE 0 END),    NA2=SUM(CASE WHEN (ZBLYDM like '0601%') AND YSDWDM='117007' THEN JE ELSE 0 end),    NA3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND YSDWDM='117007' THEN JE ELSE 0 end),    ZS1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') AND (YSDWDM<>'117' AND YSDWDM<>'117001' AND YSDWDM<>'117002' AND YSDWDM<>'117003' AND YSDWDM<>'117004' AND YSDWDM<>'117005' AND YSDWDM<>'117006' AND YSDWDM<>'117007')  THEN JE ELSE 0 END),    ZS2=SUM(CASE WHEN (ZBLYDM like '0601%') AND (YSDWDM<>'117' AND YSDWDM<>'117001' AND YSDWDM<>'117002' AND YSDWDM<>'117003' AND YSDWDM<>'117004' AND YSDWDM<>'117005' AND YSDWDM<>'117006' AND YSDWDM<>'117007') THEN JE ELSE 0 end),    ZS3=SUM(CASE WHEN (ZBLYDM like '0401%'or ZBLYDM like '0501%') AND (YSDWDM<>'117' AND YSDWDM<>'117001' AND YSDWDM<>'117002' AND YSDWDM<>'117003' AND YSDWDM<>'117004' AND YSDWDM<>'117005' AND YSDWDM<>'117006' AND YSDWDM<>'117007') THEN JE ELSE 0 end)from ZB_MXZB  WHERE (ZBZT<>'2')  and (substring(sh_rq,1,6)<='201109')  and (shr_id<>-1) ----预算执行分析前台参数param设置GROUP BY KJND,GSDM,YSKMDM) AS BGROUP BY KJND,GSDM,FZDM,FZMCORDER BY FZDM--感觉这样写SQL，查询的速度比较慢，2000多条记录，查询了14s，不知道怎么优化下。--我想在上面查询出的数据按照FZDM（FZDM级次：3-2-2）分级汇总：如结果:KJND    GSDM    FZDM     FZMC                    ZBZE1     ZBZE2     ZBZE3  .........2011    888    201    一般公共服务               1500      3000      4000   .........2011    888    20101      人大事务               1500      3000      4000   .........2011    888    2010101       行政运行            1000      2000      2000   .........2011    888    2010102       一般行政管理事务    500       1000      2000   ............