日期:2014-05-18 浏览次数:20542 次
--- 您辛苦了![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 #A from gl_fzxzl WHERE FZDM LIKE '2%') AS A */ select distinct fzdm,case when len(fzdm)=3 then fzmc when len(fzdm)=5 then ' '+fzmc when 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 #T2 SELECT 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 #T2 FROM /*(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 ' '+fzmc when 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