- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 如何实现多对多的记录生成？ (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=179359)
-- 发布时间：2022/8/19 23:54:00
-- 如何实现多对多的记录生成？
如何实现根据《订单》和《用量》生成《用量明细》的记录，是生成明细记录，不是帮助中的BOM汇总？ 此主题相关图片如下：生成明细.jpg |
-- 发布时间：2022/8/20 9:17:00
--
根据编号和名称进行关联，做个查询表即可：http://www.foxtable.com/webhelp/topics/2322.htm
-- 发布时间：2022/8/20 13:32:00
--
1.用查询表试一下，在“条件”：{订单}.[开票情况] =\'已开票\'，{订单}.[付款] =1，不执行，是什么原因，如何处理？谢谢
2.之前，查询器好象有一个可以，查看代码，现在只SQL语句，这个是删除？
-- 发布时间：2022/8/20 13:39:00
--
查询设计器里点击预览按钮，就可以看到sql了
-- 发布时间：2022/8/20 14:59:00
--
1，如何不用查询器，用代码能实现？如何实现？谢谢
2
.用查询表试一下，在“条件”：{订单}.[开piao情况] =\'已开piao\'，{订单}.[付款] =1，不执行，是什么原因，如何处理？谢谢 |
-- 发布时间：2022/8/20 15:15:00
--
1、使用代码执行SQL参考：http://www.foxtable.com/webhelp/topics/0696.htm
2、应该是没有符合这个条件的数据
-- 发布时间：2022/8/23 15:15:00
--
用下面代码，不能实现？
Dim cmd As New SQLCommand
cmd.C
Dim dt As DataTable
Dim Cols1() As String = {"产品ID", "产品名称"}
Dim Cols2() As String = {"产品ID", "产品名称"}
cmd.CommandText = "SELECK * From {表A} Where {表A}.[产品ID] = {多张表合并}.[产品ID])"
dt = cmd.ExecuteReader()
For Each dr1 As DataRow In dt.DataRows
Dim dr2 As DataRow = DataTables("多张表合并").AddNew()
For i As Integer = 0 To Cols1.Length - 1
dr2(Cols2(i)) = dr1(Cols1(i))
Next
Next
Dim cmd2 As New SQLCommand
cmd2.C
Dim dt2 As DataTable
Dim Cols3() As String = {"客户ID", "客户ID"}
Dim Cols4() As String = {"客户ID", "客户ID"}
cmd2.CommandText = "SELECK * From {表B} Where {表B}.[客户ID] = {多张表合并}.[客户ID])"
dt2 = cmd2.ExecuteReader()
For Each dr1 As DataRow In dt2.DataRows
Dim dr2 As DataRow = DataTables("多张表合并").AddNew()
For i As Integer = 0 To Cols1.Length - 1
dr2(Cols4(i)) = dr1(Cols3(i))
Next
Next
Dim cmd3 As New SQLCommand
cmd3.C
Dim dt3 As DataTable
Dim Cols5() As String = {"产品ID", "产品名称", "客户ID", "客户名称", "单价", "折扣", "数量", "日期" }
Dim Cols6() As String = {"产品ID", "产品名称", "客户ID", "客户名称", "单价", "折扣", "数量", "日期" }
cmd3.CommandText = "SELECK * From {订单A} Where {订单A}.[产品ID] = {多张表合并}.[产品ID] and {订单A}.[客户ID] = {多张表合并}.[客户ID] "
dt3 = cmd3.ExecuteReader()
For Each dr1 As DataRow In dt3.DataRows
Dim dr2 As DataRow = DataTables("多张表合并").AddNew()
For i As Integer = 0 To Cols1.Length - 1
dr2(Cols6(i)) = dr1(Cols5(i))
Next
Next
\'查询器生成的SQL语句：
\'SelecK{订单}.产品ID, 产品名称, 单价, 折扣, 数量, 日期, 选中, 开票情况, {订单}.客户ID, 客户名称 From (({订单} Inner Join {产品} On {产品}.[产品ID] = {订单}.[产品ID]) Inner Join {客户} On {客户}.[客户ID] = {订单}.[客户ID])
-- 发布时间：2022/8/23 15:19:00
--
没看懂。这代码想干嘛？
-- 发布时间：2022/8/23 15:33:00
--
Dim jb As New SQLJoinTableBuilder("代码查询器_表1", "订单")
jb.C
jb.AddTable("订单", "产品ID", "产品", "产品ID")
jb.AddTable("订单", "客户ID", "客户", "客户ID")
jb.AddCols("产品名称", "客户名称", "日期", "数量", "单价")
jb.AddExp("金额", "数量 * 单价")
jb.Build()
MainTable = Tables("代码查询器_表1")
老师，想用上面代码实现，其查询结果在窗口上的表1显示，如何实现？
-- 发布时间：2022/8/23 15:39:00
--
Dim jb As New SQLJoinTableBuilder("代码查询器_表1", "订单")
jb.C
jb.AddTable("订单", "产品ID", "产品", "产品ID")
jb.AddTable("订单", "客户ID", "客户", "客户ID")
jb.AddCols("产品名称", "客户名称", "日期", "数量", "单价")
jb.AddExp("金额", "数量 * 单价")
Tables("代码查询器_表1").DataSource = jb.BuildDataSource()