树形数据汇总查询 - -
--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 ............
ZBLYDM--都有些什么值？不在select条件可用这滤处理if OBJECT_ID('Tempdb..#A') is not null drop table #A select distinct fzdm,case when len(fzdm)=3 then fzmc when len(fzdm)=5 then ' '+fzmc when len(fzdm)=7 then ' '+fzmc end as fzmc into #Afrom gl_fzxzl WHERE FZDM LIKE '2%') AS Aif OBJECT_ID('Tempdb..#B') is not null drop table #BSelect KJND, GSDM, YSKMDM, ZBZE1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0[4-6]01%') THEN JE ELSE 0 END), --没有合计ZBZE4的金额 ZBZE2=SUM(CASE WHEN (ZBLYDM like '0601%') THEN JE ELSE 0 end), ZBZE3=SUM(CASE WHEN (ZBLYDM like '0[45]1%') THEN JE ELSE 0 end), --ZBZE4=SUM(CASE WHEN (ZBLYDM like --未确定') THEN JE ELSE 0 end),--没有确定指标来源 JP1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117001' THEN JE ELSE 0 end), LH1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117002' THEN JE ELSE 0 end), CH1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117004' THEN JE ELSE 0 end), HJ1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117003' THEN JE ELSE 0 end), CY1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117005' THEN JE ELSE 0 end), CN1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117006' THEN JE ELSE 0 end), NA1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') 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 '0[45]01%') AND YSDWDM='117007' THEN JE ELSE 0 end), ZS1=SUM(CASE WHEN (ZBLYDM like '0[4-6]01%') AND (YSDWDM<>'117' AND YSDWDM not like '11700[1-7]') THEN JE ELSE 0 END), ZS2=SUM(CASE WHEN (ZBLYDM like '0601%') AND (YSDWDM<>'117' AND YSDWDM not like '11700[1-7]') THEN JE ELSE 0 end), ZS3=SUM(CASE WHEN (ZBLYDM like '0[45]01%') AND YSDWDM<>'117' and YSDWDM not like '11700[1-7]' THEN JE ELSE 0 end)into #Bfrom ZB_MXZB WHERE (ZBZT<>'2') and (substring(sh_rq,1,6)<='201109') and (shr_id<>-1) ----预算执行分析前台参数param设置SELECT 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 #A as a, #B as bGROUP BY KJND,GSDM,FZDM,FZMCORDER BY FZDM
[解决办法]
插入临时表就行了 另外问下 你的A,B两个子查询之间没有关联么？那样得到的是笛卡尔积 也会很慢的
[解决办法]
查询速度慢的原因是,你用了很多的 like.