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

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("统计表")

仔細想想你要達到的結果:合并[入库明细]和[兑换明细],同時連接[入庫單][兌換表]及[客戶信息]三表對應信息,因此,單單JOIN是不能的,還要UNION才行!

因此7樓的代碼可能在[客户编号]及[操作日期]都相同時有可能有誤差:連接在同一行而不是分行顯示

SQL的:

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

Union

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

order by 客户编号,操作日期

[此贴子已经被作者于2013-4-29 11:48:03编辑过]

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编辑过]

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