SQL code--- 您辛苦了！[img=http://hi.csdn.net/attachment/201108/17/4981413_1313567397Jdb8.gif][/img]if OBJECT_ID('Tempdb..#T') is not null drop table #T /* select distinct fzdm,space(len(fzdm)-3)+fzmc end as fzmc into #Afrom gl_fzxzl WHERE FZDM LIKE '2%') AS A*/select distinct fzdm,case when len(fzdm)=3 then fzmc when len(fzdm)=5 then ' '+fzmcwhen len(fzdm)=7 then ' '+fzmc end fzmc into #T from gl_fzxzl WHERE FZDM LIKE '2%' and gsdm='888' and kjnd='2011'if OBJECT_ID('Tempdb..#T2') is not null drop table #T2SELECT DISTINCT A.KJND,A.GSDM,A.FZDM AS YSKMDM,A.FZMC,--case when grouping(FZMC)=0 then B.KJND else '' end as KJND,--case when grouping(FZMC)=0 then B.GSDM else '' end as GSDM,--case when grouping(FZMC)=0 then A.FZDM else '' end as FZDM,--case when GROUPING(FZDM)=1 then '合计' else A.FZMC end as FZMC, ZBZE1=isnull(SUM( ZBZE1 ),0), ZBZE2=isnull(SUM( ZBZE2 ),0), ZBZE3=isnull(SUM( ZBZE3 ),0), --ZBZE4=isnull(SUM( ZBZE1 ),0), JP1=isnull(SUM( JP1 ),0), JP2=isnull(SUM( JP2 ),0), JP3=isnull(SUM( JP3 ),0), LH1=isnull(SUM( LH1 ),0), LH2=isnull(SUM( LH2 ),0), LH3=isnull(SUM( LH3 ),0), CH1=isnull(SUM( CH1 ),0), CH2=isnull(SUM( CH2 ),0), CH3=isnull(SUM( CH3 ),0), HJ1=isnull(SUM( HJ1 ),0), HJ2=isnull(SUM( HJ2 ),0), HJ3=isnull(SUM( HJ3 ),0), CY1=isnull(SUM( CY1 ),0), CY2=isnull(SUM( CY2 ),0), CY3=isnull(SUM( CY3 ),0), CN1=isnull(SUM( CN1 ),0), CN2=isnull(SUM( CN2 ),0), CN3=isnull(SUM( CN3 ),0), NA1=isnull(SUM( NA1 ),0), NA2=isnull(SUM( NA2 ),0), NA3=isnull(SUM( NA3 ),0), ZS1=isnull(SUM( ZS1 ),0), ZS2=isnull(SUM( ZS2 ),0), ZS3=isnull(SUM( ZS3 ),0)into #T2FROM/*(select distinct fzdm,space(len(fzdm)-3)+fzmc as fzmc from gl_fzxzl WHERE FZDM LIKE '2%' and gsdm='888' and kjnd='2011') AS A --会计年度，公司代码的参数设置*/(select distinct kjnd,gsdm,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%' and gsdm='888' and kjnd='2011') AS A --会计年度，公司代码的参数设置left join (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) ZS1=SUM(CASE WHEN (ZBLYDM like '0601%'or ZBLYDM like '0501%' or ZBLYDM like '0401%') 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 '0401%'or ZBLYDM like '0501%') AND YSDWDM<>'117' and YSDWDM not like '11700[1-7]' THEN JE ELSE 0 end)from ZB_MXZB WHERE (ZBZT<>'2') and (substring(sh_rq,1,6)<='201109') and (shr_id<>-1) and kjnd=2011 ---公司代码、会计年度参数设置GROUP BY KJND,GSDM,YSKMDM) AS BON B.YSKMDM=A.FZDM--ON B.YSKMDM LIKE A.FZDM+'%'--ON B.YSKMDM=A.FZDM--GROUP BY A.FZDM,B.KJND,B.GSDM,A.FZMC with rollup --having (GROUPING(FZDM)=1 or grouping(FZMC)=0)--ORDER BY GROUPING(FZDM) desc,FZDMGROUP BY A.KJND,A.GSDM,A.FZDM,A.FZMCORDER BY A.FZDMCreate index IX_#T on #T(FZDM)Create index IX_#T2 on #T2(YSKMDM)--SELECT * FROM #T A--SELECT * FROM #T2 BSELECT --case when grouping(FZMC)=0 then B.KJND else '' end as KJND,--case when grouping(FZMC)=0 then B.GSDM else '' end as GSDM,case when grouping(A.FZDM)=0 then A.FZDM else '' end as FZDM,case when GROUPING(A.FZMC)=1 then '合计' else A.FZMC end as FZMC,--A.FZDM,A.FZMC,SUM(B.ZBZE1) AS T_ZBZE1,SUM(B.ZBZE2) AS T_ZBZE2,SUM(B.ZBZE3) AS T_ZBZE3,--SUM(B.ZBZE4) AS T_ZBZE4,SUM(B.JP1) AS T_JP1,SUM(B.JP2) AS T_JP2,SUM(B.JP3) AS T_JP3,SUM(B.LH1) AS T_LH1,SUM(B.LH2) AS T_LH2,SUM(B.LH3) AS T_LH3,SUM(B.CH1) AS T_CH1,SUM(B.CH2) AS T_CH2,SUM(B.CH3) AS T_CH3,SUM(B.HJ1) AS T_HJ1,SUM(B.HJ2) AS T_HJ2,SUM(B.HJ3) AS T_HJ3,SUM(B.CY1) AS T_CY1,SUM(B.CY2) AS T_CY2,SUM(B.CY3) AS T_CY3,SUM(B.CN1) AS T_CN1,SUM(B.CN2) AS T_CN2,SUM(B.CN3) AS T_CN3,SUM(B.NA1) AS T_NA1,SUM(B.NA2) AS T_NA2,SUM(B.NA3) AS T_NA3,SUM(B.ZS1) AS T_ZS1,SUM(B.ZS2) AS T_ZS2,SUM(B.ZS3) AS T_ZS3--T_ZBZE1=isnull(SUM( B.ZBZE1 ),0)FROM #T AS ALEFT JOIN #T2 AS B--ON B.YSKMDM LIKE A.FZDM+'%'--ON ltrim(rtrim(B.YSKMDM)) like ltrim(rtrim(A.FZDM))+'%'ON rtrim(B.YSKMDM) like rtrim(A.FZDM)+'%'WHERE LEN(A.FZDM)<>7GROUP BY A.FZDM,A.FZMC with rollup having (GROUPING(A.FZDM)=1 or grouping(A.FZMC)=0)ORDER BY GROUPING(A.FZDM) desc,A.FZDM--GROUP BY A.FZDM,A.FZMC--ORDER BY A.FZDM