关闭

用友--客户科目余额表sql语句:注意两条语句结果集的合并

标签: sqlnulljoinc
2418人阅读 评论(0) 收藏 举报
分类:

Select Code.cCode as NaCo1,Code.ccode_name as Name1,Customer.cCusCode as NaCo2,Customer.cCusAbbName as Name2,Sum(case when iperiod=3 then (case when cbegind_c<>'贷' then mb else -mb end) else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,Sum(case when iperiod=3 then (case when cendd_c<>'贷' then me else -me end) else 0 end) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then mb_f else -mb_f end) else 0 end) end) as mbb_f,Sum(case when code.cexch_name is null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then me_f else -me_f end) else 0 end) end) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then nb_s else -nb_s end) else 0 end) end) as nbb_s,Sum(case when code.cmeasure is null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then ne_s else -ne_s end) else 0 end) end) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accass ON Code.cCode=GL_accass.cCode) ON Customer.cCusCode=GL_accass.ccus_id  where iPeriod <= 3 and not GL_accass.cCode is Null and not GL_accass.ccus_id is Null  and code.bcus=1  and GL_accass.ccode=N'112202' Group By Code.cCode,Code.ccode_name,Customer.cCusCode,Customer.cCusAbbName UNION  Select '柞柞柞' as Naco1,'柞柞柞' as Name1,'柞柞柞' as Naco2,'柞柞柞' as Name2,Sum(case when iperiod=3 then (case when cbegind_c<>'贷' then mb else -mb end) else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,Sum(case when iperiod=3 then (case when cendd_c<>'贷' then me else -me end) else 0 end) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then mb_f else -mb_f end) else 0 end) end) as mbb_f,Sum(case when code.cexch_name is null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then me_f else -me_f end) else 0 end) end) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then nb_s else -nb_s end) else 0 end) end) as nbb_s,Sum(case when code.cmeasure is null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then ne_s else -ne_s end) else 0 end) end) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accass ON Code.cCode=GL_accass.cCode) ON Customer.cCusCode=GL_accass.ccus_id   where iperiod<=3 and not GL_accass.cCode is Null  and code.bcus=1  and GL_accass.ccode=N'112202' and not GL_accass.ccus_id is Null Order By NaCo1,NaCo2
------------------------------------------------------------------------------------------------------------------------

Select Code.cCode as NaCo1,Code.ccode_name as Name1,Customer.cCusCode as NaCo2,Customer.cCusAbbName as Name2,Sum(case when iperiod<3 then md-mc else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,(Sum(case when iperiod<3 then md-mc else 0 end) + Sum(case when iperiod>=3 then md else 0 end) - Sum(case when iperiod>=3 then mc else 0 end)) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) as mbb_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,(Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) + Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) - Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end)) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) as nbb_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,(Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) + Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) - Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end)) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accvouch ON Code.cCode=GL_accvouch.cCode) ON Customer.cCusCode=GL_accvouch.ccus_id  where iperiod<= 3 and not GL_accvouch.cCode is Null and not GL_accvouch.ccus_id is Null  and code.bcus=1  and GL_accvouch.ccode=N'112202' and ibook=0 and (iflag is null or iflag=2) and iperiod>=1  Group By Code.cCode,Code.ccode_name,Customer.cCusCode,Customer.cCusAbbName UNION  Select '柞柞柞' as Naco1,'柞柞柞' as Name1,'柞柞柞' as Naco2,'柞柞柞' as Name2,Sum(case when iperiod<3 then md-mc else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,(Sum(case when iperiod<3 then md-mc else 0 end) + Sum(case when iperiod>=3 then md else 0 end) - Sum(case when iperiod>=3 then mc else 0 end)) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) as mbb_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,(Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) + Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) - Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end)) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) as nbb_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,(Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) + Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) - Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end)) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accvouch ON Code.cCode=GL_accvouch.cCode) ON Customer.cCusCode=GL_accvouch.ccus_id   where iperiod<=3 and not GL_accvouch.cCode is Null  and code.bcus=1  and GL_accvouch.ccode=N'112202' and  not GL_accvouch.ccus_id is Null and ibook=0 and (iflag is Null or iflag=2) and iperiod>=1 Order By NaCo1,NaCo2

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:25002次
    • 积分:325
    • 等级:
    • 排名:千里之外
    • 原创:4篇
    • 转载:16篇
    • 译文:1篇
    • 评论:2条
    最新评论