问题:通过SQL查询汇总二套帐上的数据。
要求格式如下:
----------------------------------------------------------------------------------
编号 | 客户名称 | 业务员姓名 | 期初余额 | 本期借发生数|本期贷方发生数| 期末数|
----------------------------------------------------------------------------------
表结构:
现在要从二张表上取数,一张是客户表:Customer,一张是数据表:GL_accvouch。在Customer内有二个字段需要用到:编号:cCusCode,客户名称:cCusName。
在GL_accvouch内有4字段需要用到:客户编号:ccus_id,业务员:cname,借方数(MD),贷方数(mc),另加一些条件。
其中GL_accvouch.ccus_id=Customer.cCusCode。我将SQL写成如下:如果把cname业务员这个字段去掉,我能得到结果,问题现在就在业务员这个字段上。SQL:
sql1 = "select C.cCusCode,C.cCusName,S.cname,K.qc-K.qc1,S.fs,L.hk,K.qc-K.qc1+S.fs-L.hk from (((UFDATA_004_2007.dbo.Customer as C" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as qc,Sum(mc) as qc1 from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod ='0' Group By ccus_id,cname)K ON C.cCusCode=K.ccus_id )" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as fs from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod >='" & iPeriod2 & "' and iperiod <= '" & iPeriod2 & "' Group By ccus_id,cname)S ON C.cCusCode=S.ccus_id )" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as hk from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod >='" & iPeriod2 & "' and iperiod <= '" & iPeriod2 & "' Group By ccus_id,cname)L ON C.cCusCode=L.ccus_id )"
要求格式如下:
----------------------------------------------------------------------------------
编号 | 客户名称 | 业务员姓名 | 期初余额 | 本期借发生数|本期贷方发生数| 期末数|
----------------------------------------------------------------------------------
表结构:
现在要从二张表上取数,一张是客户表:Customer,一张是数据表:GL_accvouch。在Customer内有二个字段需要用到:编号:cCusCode,客户名称:cCusName。
在GL_accvouch内有4字段需要用到:客户编号:ccus_id,业务员:cname,借方数(MD),贷方数(mc),另加一些条件。
其中GL_accvouch.ccus_id=Customer.cCusCode。我将SQL写成如下:如果把cname业务员这个字段去掉,我能得到结果,问题现在就在业务员这个字段上。SQL:
sql1 = "select C.cCusCode,C.cCusName,S.cname,K.qc-K.qc1,S.fs,L.hk,K.qc-K.qc1+S.fs-L.hk from (((UFDATA_004_2007.dbo.Customer as C" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as qc,Sum(mc) as qc1 from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod ='0' Group By ccus_id,cname)K ON C.cCusCode=K.ccus_id )" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as fs from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod >='" & iPeriod2 & "' and iperiod <= '" & iPeriod2 & "' Group By ccus_id,cname)S ON C.cCusCode=S.ccus_id )" & _
" LEFT JOIN " & _
"(Select ccus_id,cname,Sum(md) as hk from UFDATA_004_2007.dbo.GL_accvouch where ccode='1131'and ibook='1'and iperiod >='" & iPeriod2 & "' and iperiod <= '" & iPeriod2 & "' Group By ccus_id,cname)L ON C.cCusCode=L.ccus_id )"
解决方案 »
- richtextbox与picturebox的连接问题
- 大家进来吧!
- 给各位一个练手的机会!!!!!!!!!!!!!!!
- 问问各位:有没有医院体检系统的需求分析报告,或者相关介绍?紧急等待中。。。
- 请教怎样编程实现datalist控件的数据绑定?
- 哪位老板要人。深圳想找兼职(网站开发。c/s b/s都行)。熟悉语言vb asp jsp vc etc...,有三年经验
- 想开公司了,兄弟们帮帮忙啊
- 那位大虾知道???我要通过单击(dataGrid_Click)对dataGrid中的某一行定位,但是程序运行时中要双击才能准确定位???这是为什么??
- 一个问题
- 关于做数据库管理软件
- 如何将数据库某列数据用for each 放到一个Button控件数组的Caption中
- VB编写的程序在VISTA里运行错误
--------------------
个人认为表设计有问题,应该有一个业务员信息表,这样GL_accvouch表中通过业务员编号来关联业务员信息。你现在这样GL_accvouch表中直接使用cname名称,又用了好几个子查询并用cname分组,如果各条数据中cname不正确(比如“张三”与“张三 ”),整个SQL语句就有可能得不到正确结果了
你试试用isnull函数处理一下,看看会不会出结果
比如:
select C.cCusCode,C.cCusName,S.cname,isnull(K.qc,0).......这样
会计科目代码ccode (...)
记帐标志ibook (只有0和1 )
具体条件是什么写出来
isnull(sum(case when iperiod ='0' then md else 0 end),0)-isnull(sum(case when iperiod ='0' then mc else 0 end),0) as 起初
,isnull(sum(case when iperiod >0 and iperios<5 then md else 0 end),0) as 本期届
,isnull(sum(case when iperiod >0 and iperios<5 then mc else 0 end),0) as 本期dai,
isnull(sum(case when iperiod ='0' then md else 0 end),0)-isnull(sum(case when iperiod ='0' then mc else 0 end),0)
+ isnull(sum(case when iperiod >0 and iperios<5 then md else 0 end),0) as 本期届 -isnull(sum(case when iperiod >0 and iperios<5 then mc else 0 end),0) as 期末
from Customer a,GL_accvouch b where a.cCusCode=b.ccus_id and b.ccode='1131'and b.ibook='1'