日期:2014-05-18  浏览次数:20542 次

感谢昨天【roy_88】周末都在帮我处理SQL,并与大家分享!
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 #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