以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  合并查询表按日期升序  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=148234)

--  作者:lal021962
--  发布时间:2020/4/2 19:37:00
--  合并查询表按日期升序

以下代码,请问如何让合并后的查询表按日期升序排列。

 

 

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("付款单编号","{资金支出单}.单据类型","账号名称",True)
jb1.AddExp("支出金额","(-支出金额)",True)
jb1.AddCols("付款日期",True)
jb1.filter = "账号名称 =\'" & k &  "\' and 付款日期>=\'" & g & "\' and 付款日期<=\'" & f & "\'"

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

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 & "\' and {工厂销售收款单}.收款日期<=\'" & f & "\'"

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

Dim jb5 As New SQLJoinTableBuilder("查询表1","资金账号")
jb5.C
jb5.AddTable("资金账号","账号编号","易装店销售收款单","账号编号")
jb5.AddTable("易装店销售收款单","收款单编号","应收款明细","销售收款单编号")
jb5.AddCols("{易装店销售收款单}.收款单编号","{易装店销售收款单}.表名代码","账号名称",True)
jb5.AddExp("实际总收款", "isnull(Sum({应收款明细}.本次收款),0)-isnull(Sum({应收款明细}.本次抹零),0)")
jb5.AddCols("{易装店销售收款单}.收款日期",True)
jb5.filter = "账号名称 =\'" & k &  "\' and {易装店销售收款单}.收款日期>=\'" & g & "\' and {易装店销售收款单}.收款日期<=\'" & f & "\'"

Dim jb6 As New SQLJoinTableBuilder("查询表1","资金账号")
jb6.C
jb6.AddTable("资金账号","账号编号","自产付款单","账号编号")
jb6.AddTable("自产付款单","付款单编号","自产付款明细表","付款单编号")
jb6.AddCols("{自产付款单}.付款单编号","{自产付款单}.表名代码","账号名称",True)
jb6.AddExp("实际总收款", "-isnull(Sum({自产付款明细表}.本次付款金额),0)+isnull(Sum({自产付款明细表}.抹零金额),0)")
jb6.AddCols("{自产付款单}.付款日期",True)
jb6.filter = "账号名称 =\'" & k &  "\' and {自产付款单}.付款日期>=\'" & g & "\' and {自产付款单}.付款日期<=\'" & f & "\'"

Dim jb7 As New SQLJoinTableBuilder("查询表1","资金账号")
jb7.C
jb7.AddTable("资金账号","账号编号","自产付款单","账号编号")
jb7.AddTable("自产付款单","付款单编号","安装付款明细表","付款单编号")
jb7.AddCols("{自产付款单}.付款单编号","{自产付款单}.表名代码","账号名称",True)
jb7.AddExp("实际总收款", "isnull(-Sum({安装付款明细表}.本次付款金额),0)+isnull(Sum({安装付款明细表}.抹零金额),0)")
jb7.AddCols("{自产付款单}.付款日期",True)
jb7.filter = "账号名称 =\'" & k &  "\' and {自产付款单}.付款日期>=\'" & g & "\' and {自产付款单}.付款日期<=\'" & f & "\'"

Dim jb8 As New SQLJoinTableBuilder("查询表1","资金账号")
jb8.C
jb8.AddTable("资金账号","账号编号","采购付款单","账号编号")
jb8.AddTable("采购付款单","付款单编号","采购订单明细表","采购付款单编号")
jb8.AddCols("{采购付款单}.付款单编号","{采购付款单}.表名代码","账号名称",True)
jb8.AddExp("实际总收款", "-isnull(Sum({采购订单明细表}.本次付款金额),0)+isnull(Sum({采购订单明细表}.抹零金额),0)")
jb8.AddCols("{采购付款单}.付款日期",True)
jb8.filter = "账号名称 =\'" & k &  "\' and {采购付款单}.付款日期>=\'" & g & "\' and {采购付款单}.付款日期<=\'" & f & "\'"

jb1.Build()
jb1.Union(jb2) \'组合jb2
jb1.Union(jb3) \'组合jb2
jb1.Union(jb4) \'组合jb2
jb1.Union(jb5) \'组合jb2
jb1.Union(jb6) \'组合jb2
jb1.Union(jb7) \'组合jb2
jb1.Union(jb8) \'组合jb2

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

DataTables("资金流向明细表_Table1").DataCols.Add("余额",Gettype(Double))
Dim sum As Double = val(e.Form.Controls("Label6").text)
For Each dr As Row In Tables("资金流向明细表_Table1").Rows
    sum = sum + dr("支出金额")
    dr("余额") = sum
Next


--  作者:有点蓝
--  发布时间:2020/4/2 20:12:00
--  
……

jb1.Union(jb8) \'组合jb2

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

Tables("资金流向明细表_Table1").sort = “付款日期

DataTables("资金流向明细表_Table1").DataCols.Add("余额",Gettype(Double))

……