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

原创 2012年03月30日 15:52:39

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

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

使用Log4j2打印Mybatis SQL语句以及结果集

配置log4j2.xml 实现用Log4j2来打印Mybatis的SQL很简单,先配置一个name为consolePrint的附加器,指定输出格式  然后在loggers下配置一个logger,n...

如何在SQL Server查询语句(Select)中检索存储过程(Store Procedure)的结果集?(

如何在SQL Server查询语句(Select)中检索存储过程(Store Procedure)的结果集?(2006-12-14 09:25:36) 与这个问题具有相同性质的其他描述还...

PDO进行sql语句预处理和操作结果集详细介绍(二)

PDO进行sql语句预处理和操作结果集详细介绍(二)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: 该语句没有返回结果集

1、错误描述org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized S...

查询语句之Union联合结果集

Union对查询语句进行联合结果集 :基本的原则:每个结果集必须有相同的列数;每个结果集的列必须类型相容 1、使用Union执行对student表查询联合结构集 select * from s...

Java大牛养成记——一条SQL语句更新同一张表中的两条数据

背景:项目中的需求是这样的:一堆图片中只有一张图片的状态是“启用”的,其他图片的状态是“未启用”。点击某张图片下面的启用按钮,这张图片的状态为“启用”,同时其他图片的状态修改为“未启用”。想象一下这个...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)