各位老师，关于多表查询表的问题【已解决,Don高手的SQL,各位狐友看看】 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

您太高了！！真不知说什么好了！！图片点击可在新窗口打开查看

以下是引用don在2013-4-28 22:06:00的发言：

Dim jb As new SQLJoinTableBuilder("统计表","入库明细")

Dim dt1 As fxDataSource

jb.AddTable("入库明细","入库编号","入库单","入库编号")

jb.AddExp("客户编号","客户编号")

jb.AddExp("客户名称","商品编号")

jb.AddExp("入库编号","{入库单}.入库编号")

jb.Addexp("存入商品","商品编号")

jb.Addexp("操作日期","存入日期")

jb.Addexp("入库数量","数量")

jb.Addexp("入库单价","单价")

jb.Addexp("入库金额","金额")

dt1 = jb.BuildDataSource()

jb = new SQLJoinTableBuilder("A1","兑换明细")

Dim dt2 As fxDataSource

jb.AddTable("兑换明细","兑换编号","兑换表","兑换编号")

jb.AddCols("客户编号","{兑换明细}.兑换编号")

jb.Addexp("兑换商品","商品编号")

jb.Addexp("操作日期","兑换日期")

jb.Addexp("兑换数量","数量")

jb.Addexp("兑换单价","单价")

jb.Addexp("兑换金额","金额")

dt2 = jb.BuildDataSource()

Dim nms As String() = {"客户编号","操作日期"} '指定连接列

dt1.Combine(nms,dt2,nms)

dt1.Show("统计表") '显示统计结果

Tables("统计表").StopRedraw

For Each r As Row In Tables("统计表").Rows

Dim dr As DataRow = DataTables("客户信息").Find("客户编号 = '" & r("客户编号") & "'")

If dr IsNot Nothing Then

r("客户名称") = dr("客户名称")

End If

Tables("统计表").ResumeRedraw

Tables("统计表").sort="操作日期"

MainTable = Tables("统计表")

一个单子兑换明细是两笔就出错了

[此贴子已经被作者于2013-4-30 10:31:18编辑过]

就是兑换明细里就只能有一条记录

此主题相关图片如下：未命名.jpg

[此贴子已经被作者于2013-4-30 10:35:44编辑过]

Dim v1 As String() = {"客户编号","客户名称","入库编号","存入商品","操作日期","入库数量", _

"入库单价","入库金额","兑换编号","兑换商品","兑换数量","兑换单价","兑换金额"}

Dim v2 As String() = {"{客户信息}.客户编号","客户名称","{入库单}.入库编号", _

"商品编号","存入日期","数量","单价","数量*单价","''","''","0","0","0"}

Dim v3 As String() = {"{客户信息}.客户编号","客户名称","''","''","兑换日期", _

"0","0","0","{兑换明细}.兑换编号","商品编号","数量","单价","数量*单价"}

Dim jb As new SQLJoinTableBuilder("统计表","入库明细")

jb.AddTable("入库明细","入库编号","入库单","入库编号",JoinModeEnum.Left)

jb.AddTable("入库单","客户编号","客户信息","客户编号",JoinModeEnum.Left)

For n1 As Integer = 0 To v1.length-1

jb.AddExp(v1(n1),v2(n1))

Dim jb2 As new SQLJoinTableBuilder("A1","兑换明细")

jb2.AddTable("兑换明细","兑换编号","兑换表","兑换编号",JoinModeEnum.Left)

jb2.AddTable("兑换表","客户编号","客户信息","客户编号",JoinModeEnum.Left)

For n1 As Integer = 0 To v1.length-1

jb2.AddExp(v1(n1),v3(n1))

jb.Union(jb2,True)

Dim dt1 As fxDataSource = jb.BuildDataSource()

dt1.show("统计表")

Dim t1 As Table = Tables("统计表")

t1.sort = "客户编号,操作日期"

MainTable = t1

t1.Select(0,0,t1.Rows.count-1,t1.Cols.count-1)

Syscmd.Edit.ClearZero()

t1.Select(0,0)

[此贴子已经被作者于2013-4-30 13:50:06编辑过]

連接分對接,左接及右接,需明確指出(如不指出，默認為對接 INNER JOIN):

對接: INNER JOIN (JoinModeEnum.Inner)=========>要求两个表必须都有对应的记录，才会在查询表中生成记录;

左接：LEFT JOIN (JoinModeEnum.Left)==================>只要求左边的表有对应的记录即可，

右接：RIGHT JOIN (JoinModeEnum.Right)=================>只要求右边的表有对应的记录即可。

Select b.客户编号,客户名称,b.入库编号,商品编号 as 存入商品,存入日期 as 操作日期,数量 As 入库数量,单价 As 入库单价,数量*单价 As 入库金额,'' As 兑换编号,'' as 兑换商品,0 As 兑换数量,0 As 兑换单价,0 As 兑换金额 From ({入库明细} a LEFT JOIN {入库单} b ON a.入库编号 = b.入库编号) LEft JOIN {客户信息} c ON c.客户编号 = b.客户编号

Union

Select b.客户编号,客户名称,'' As 入库编号,'' as 存入商品,兑换日期 as 操作日期,0 As 入库数量,0 As 入库单价,0 As 入库金额,'' As 兑换编号,商品编号 as 兑换商品,数量 As 兑换数量,单价 As 兑换单价,数量*单价 As 兑换金额 From ({兑换明细} a LEFT JOIN {兑换表} b ON a.兑换编号 = b.兑换编号) LEFT JOIN {客户信息} c ON c.客户编号 = b.客户编号

order by 客户编号,操作日期

[此贴子已经被作者于2013-4-30 15:08:53编辑过]

再次感谢！这四个语句堪称经典了！！我要好好学习一下您给我的所有语句！大恩不言谢！还是要谢谢！

昨天晚上都两点了！我还没有弄明白狐爸的SQLJoinTableBuilder，因有些没有更详细的说明，你提供的太好了!

我争取今天好好测试，争取吃透！！

主题：各位老师，关于多表查询表的问题【已解决,Don高手的SQL,各位狐友看看】