4个表联合查询，多表查询问题 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

有4个表，prsub(申请单），posub(订单), inssub(入库单), lysub(领用单) ，现在想汇总下针对每一个 (PR子表流水号)，申请数量，订单数量，入库数量，领用数量的情况。

现在写了三个SQL语句，每个都正确，但要把这三个表放在一起不知道怎么弄了。

请老师指点，谢谢！

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.C

cmd.CommandText = "Sel ect prsub.申请单号, prsub.物料名称, prsub.规格图号, prsub.单位,prsub.工程号,prsub.站号,prsub.类别, prsub.数量, prsub.核准, prsub.PR子表流水号, b.订单数量, (prsub.数量 - isnull(b.订单数量 , 0)) As 未订数量 from {prsub} left join (Sel ect PR子表流水号, sum(数量) As 订单数量 from {posub} group by PR子表流水号) As b on prsub.PR子表流水号 = b.PR子表流水号"

dt = cmd.ExecuteReader()

Dim cmd1 As New SQLCommand

Dim dt1 As DataTable

cmd1.C

cmd1.CommandText = "Se lect prsub.PR子表流水号, c.入库数量 from {prsub} left join (Sel ect PR子表流水号, sum(入库数量) As 入库数量 from {inssub} group by PR子表流水号) As c on prsub.PR子表流水号 = c.PR子表流水号"

dt1 = cmd1.ExecuteReader()

Dim cmd2 As New SQLCommand

Dim dt2 As DataTable

cmd2.C

cmd2.CommandText = "Se lect prsub.PR子表流水号, d.领用数量 from {prsub} left join (Sel ect PR子表流水号, sum(领用数量) As 领用数量 from {lysub} group by PR子表流水号) As d on prsub.PR子表流水号 = d.PR子表流水号"

dt2 = cmd2.ExecuteReader()

比如代码

select * from (sql语句1) as aa inner join (sql语句2) as bb on aa.pr子表流水号=bb.pr子表流水号

或者，用代码组合，如

http://www.foxtable.com/webhelp/scr/2305.htm

甜老师，你好，我想用组合代码，因为sql语句连接4个表不知道怎么写，

但组合代码 - GroupTableBuilder - ，看例子，只能加分组字段和统计字段，

我想显示其他字段，如物流名称，规格型号之类的，怎么加上去？谢谢

下面是组合代码，能汇总申请数量，订单数量，入库数量，领用数量，但没有物料名称，规格型号等其他信息，不知道怎么才能加上。谢谢！

Dim bd As New GroupTableBuilder("统计表",DataTables("prsub"))

Dim dt As fxDataSource

bd.Groups.AddDef("PR子表流水号") '根据型号分组

bd.Totals.AddDef("数量","申请_数量") '对数量进行统计

dt = bd.BuildDataSource()

Dim bd1 As New GroupTableBuilder("统计表1",DataTables("posub"))

Dim dt1 As fxDataSource

bd1.Groups.AddDef("PR子表流水号") '根据型号分组

bd1.Totals.AddDef("数量","订单_数量") '对数量进行统计

dt1 = bd1.BuildDataSource()

Dim bd2 As New GroupTableBuilder("统计表2",DataTables("inssub"))

Dim dt2 As fxDataSource

bd2.Groups.AddDef("PR子表流水号") '根据型号分组

bd2.Totals.AddDef("入库数量","入库_数量") '对数量进行统计

dt2 = bd2.BuildDataSource()

Dim bd3 As New GroupTableBuilder("统计表3",DataTables("lysub"))

Dim dt3 As fxDataSource

bd3.Groups.AddDef("PR子表流水号") '根据型号分组

bd3.Totals.AddDef("领用数量","领用_数量") '对数量进行统计

dt3 = bd3.BuildDataSource()

dt.Combine("PR子表流水号",dt1,"PR子表流水号")

dt.Combine("PR子表流水号",dt2,"PR子表流水号") '将销售统计数据组合到进货统计数据

dt.Combine("PR子表流水号",dt3,"PR子表流水号") '将退货统计数据组合到进货统计数据

Tables("最全查询_Table1").DataSource = dt '将统计结果绑定到Table

用SQL 语句实现

Dim cmd As New SQLCommand

Dim dt As DataTable

cmd.C

cmd.CommandText = "Sel ect prsub.申请单号, prsub.物料名称, prsub.规格图号, prsub.单位,prsub.工程号,prsub.站号,prsub.类别, prsub.数量 as 申请数量, prsub.核准, prsub.PR子表流水号, b.订单数量, c.入库数量,d.领用数量, (prsub.数量 - isnull(b.订单数量 , 0)) As 未订数量 from {prsub} left join (Sel ect PR子表流水号, sum(数量) As 订单数量 from {posub} group by PR子表流水号) As b on prsub.PR子表流水号 = b.PR子表流水号 left join (Se lect PR子表流水号, sum(入库数量) As 入库数量 from {inssub} group by PR子表流水号) As c on prsub.PR子表流水号 = c.PR子表流水号 left join (Se lect PR子表流水号, sum(领用数量) As 领用数量 from {inssub} group by PR子表流水号) As d on prsub.PR子表流水号 = d.PR子表流水号 "

dt = cmd.ExecuteReader()

Tables("最全查询_Table1").DataSource = dt

红色地方填写列名

Dim bd As New GroupTableBuilder("统计表",DataTables("prsub"))

Dim dt As fxDataSource

bd.Groups.AddDef("物料名称")

bd.Groups.AddDef("规格型号")

bd.Groups.AddDef("PR子表流水号")

bd.Totals.AddDef("数量","申请_数量") '对数量进行统计

dt = bd.BuildDataSource()

主题：4个表联合查询，多表查询问题