主题：关于合并多个查询结果中的表达式列问题

Dim k As String = Tables("资金流向明细表").current("账号名称")
Dim g As Date = Tables("资金流向明细表").current("开始时间")
Dim f As Date = Tables("资金流向明细表").current("结束时间")

Dim jb1 As New SQLJoinTableBuilder("查询表1","资金账号")
jb1.C
jb1.AddTable("资金账号","账号编号","资金支出单","账号编号")
jb1.AddCols("付款单编号","{资金支出单}.单据类型","账号名称", "(-支出金额)","付款日期")
jb1.filter = "账号名称 ='" & k & "'"

Dim jb2 As New SQLJoinTableBuilder("查询表1","资金账号")
jb2.C
jb2.AddTable("资金账号","账号编号","资金收入单","账号编号")
jb2.AddCols("收款单编号","{资金收入单}.单据类型","账号名称", "收入金额","收款日期")
jb2.filter = "账号名称 ='" & k & "'"

Dim jb3 As New SQLJoinTableBuilder("查询表1","资金账号")
jb3.C
jb3.AddTable("资金账号","账号编号","工厂销售收款单","账号编号")
jb3.AddCols("收款单编号","{工厂销售收款单}.表名代码","收款账号","应收款金额","收款日期")
jb3.filter = "账号名称 ='" & k & "'"

jb1.Build()
jb1.Union(jb2) '组合jb2
jb1.Union(jb3)

Tables("资金流向明细表_Table1").datasource =jb1.builddatasource

 

 

以上代码中，由于jb3中的应收款金额为表达式列，造成列名无效，请问怎么办

由于有关联子表的表达式，出现了以下报错，怎么破：

 

 

 

 

 

表达式如下：

isnull(Sum(Child(对内销售收款单关联对内销售收款明细表).本次收款金额),0)-isnull(Sum(Child(对内销售收款单关联对内销售收款明细表).抹零金额),0)

此主题相关图片如下：qq图片20200324205909.png

如下代码:

Dim jb3 As New SQLJoinTableBuilder("查询表1","资金账号")
jb3.C
jb3.AddTable("资金账号","账号编号","工厂销售收款单","账号编号")
jb3.AddTable("资金账号","账号编号","对内销售收款明细表","账号编号")
jb3.AddCols("{工厂销售收款单}.收款单编号","{工厂销售收款单}.表名代码","账号名称" ,"{工厂销售收款单}.收款日期")
jb3.AddExp("实际收款金额", "isnull(Sum({对内销售收款明细表}.本次收款金额),0)-isnull(Sum({对内销售收款明细表}.抹零金额),0)")
jb3.filter = "账号名称 ='" & k &  "' and {工厂销售收款单}.收款日期>'" & g & "'" 

 

B报错：选择列表中的列 '工厂销售收款单.收款单编号' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

 

此主题相关图片如下：qq图片20200325213604.png

这样做是不会报错了，但是出现了如下问题，代码如下：

Dim k As String = Tables("资金流向明细表").current("账号名称")
Dim g As Date = Tables("资金流向明细表").current("开始时间")
Dim f As Date = Tables("资金流向明细表").current("结束时间")

Dim jb1 As New SQLJoinTableBuilder("查询表1","资金账号")
jb1.C
jb1.AddTable("资金账号","账号编号","资金支出单","账号编号")
jb1.AddCols("付款单编号","{资金支出单}.单据类型","账号名称", "(-支出金额)","付款日期")
jb1.filter = "账号名称 ='" & k &  "' and 付款日期>='" & g & "' and 付款日期<='" & f & "'"

Dim jb2 As New SQLJoinTableBuilder("查询表1","资金账号")
jb2.C
jb2.AddTable("资金账号","账号编号","资金收入单","账号编号")
jb2.AddCols("收款单编号","{资金收入单}.单据类型","账号名称", "收入金额","收款日期")
jb2.filter = "账号名称 = '" & k &  "' and 收款日期>'" & g & "'" 

Dim jb3 As New SQLJoinTableBuilder("查询表1","资金账号")
jb3.C
jb3.AddTable("资金账号","账号编号","工厂销售收款单","账号编号")
jb3.AddTable("资金账号","账号编号","对内销售收款明细表","账号编号")
jb3.AddCols("{工厂销售收款单}.收款单编号","{工厂销售收款单}.表名代码","账号名称",True)
jb3.AddExp("实际收款金额", "isnull(Sum({对内销售收款明细表}.本次收款金额),0)-isnull(Sum({对内销售收款明细表}.抹零金额),0)")
jb3.AddCols("{工厂销售收款单}.收款日期",True)
jb3.filter = "账号名称 ='" & k &  "' and {工厂销售收款单}.收款日期>'" & g & "'" 

jb1.Build()
jb1.Union(jb2) '组合jb2
jb1.Union(jb3) '组合jb2
Tables("资金流向明细表_Table1").datasource =jb1.builddatasource

 

 

 

 

导致在收款金额那一列，关联的都是此收款账号的所有金额的合计，而不是根据其父表的每一个工厂销售收款单的收款单编号来合计

 

 

此主题相关图片如下：1.png

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:客户端.rar

已解决。

请在查询表中增加一表达式列，例如列J，做到如EXCEL中一样，J7=J6+I7，I7为原查询表中列。如下图：

此主题相关图片如下：1.png

请问这个合并多个查询表的余额列该怎么添加

没有理解