用友--客户科目余额表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

金蝶EAS,后台代码查询科目余额,SQL查询科目余额

以下代码演示了如何从EAS系统查询科目余额,用于后台业务逻辑(app目录下的ControllerBean)。 /** * 查询科目余额,封装数据,返回值映射中,键:科目ID;值:科目余额队列 *...
  • guosyu
  • guosyu
  • 2017年06月06日 16:03
  • 608

微信公众平台添加深圳通余额查询功能

公交卡余额就像卫生纸一样,看着挺多的。刷着刷着就见底了。为了防患于未然。做了这样一个方便查询的微信公众接口。 原理很简单。        就是通过访问深圳通公司提供的一个查询接口  http...

EAS 一些基础数据的查询记录脚本(sql)

1.查询某类的供应商信息 ---sql     select distinct su.fnumber,su.fname_l2,cbank.FBank,cbank.FBankAccount,cbank...
  • douml88
  • douml88
  • 2016年12月29日 15:55
  • 363

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

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

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

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

Transact-SQL语句遍历结果集的三种方法

Transact-SQL语句是可以实现遍历的,有三种方法使用可以通过使用Transact-SQL语句遍历一个结果集。下面就为您详细介绍Transact-SQL语句遍历结果集的几种方法,供您参考。 一种...

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

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

用java实现把sql语句查询的结果集写到dbf文件中

这个其实就是一个导出生成DBF文件的功能,实现思路是这样:首先动态拼接一个sql语句,执行后返回结果集到代码中,再通过dbfwriter对象把结果集的数据写到dbf文件里。 1、打开数据库连接,执行s...

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

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

jdbc 调用 sql server 的存储过程时“该语句没有返回结果集”的解决方法

在JDBC中调用SQL Server中的存储过程时出现如下异常:  com.microsoft.sqlserver.jdbc.SQLServerException: 该语句没有返回结...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:用友--客户科目余额表sql语句:注意两条语句结果集的合并
举报原因:
原因补充:

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