专家坐堂-[求助]多表统计，加一个表统表，怎么总提显说SQL错误？Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  [求助]多表统计，加一个表统表，怎么总提显说SQL错误？  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=21950)

--  作者：jk362223
--  发布时间：2012/7/28 18:28:00
--  [求助]多表统计，加一个表统表，怎么总提显说SQL错误？

[求助]多表统计，加一个表统计，怎么总提显说SQL错误？我看了很久，好象没有啊，，下面是我的代码，帮我看一下：

Dim bd1 As New SQLGroupTableBuilder("报销清单","长期医嘱")
bd1.C
Dim dt1 As fxDataSource
bd1.AddTable("长期医嘱","住次ID","住院主表","住次ID")
bd1.Groups.AddDef("住次ID") \'根据品名分组
bd1.Groups.AddDef("姓名") \'根据品名分组
bd1.Groups.AddDef("参保方式") \'根据品名分组
bd1.Groups.AddDef("送报日期") \'根据品名分组
bd1.Groups.AddDef("项目ID") \'根据品名分组
bd1.Groups.AddDef("收费项目") \'根据品名分组
bd1.Groups.AddDef("项目类别") \'根据型号分组
bd1.Groups.AddDef("项目规格") \'根据品名分组
bd1.Groups.AddDef("单位") \'根据型号分组
bd1.Groups.AddDef("单价") \'根据型号分组
bd1.Totals.AddExp("长期医嘱_报数","IIF([数量] >= 0,[数量],0)") \'对数量进行统计
bd1.Totals.AddExp("长期医嘱_金额","IIF([数量] > 0,[数量] * [单价],0)") \'对数量进行统计
dt1 = bd1.BuildDataSource()

Dim bd2 As New SQLGroupTableBuilder("统计表2","临时医嘱")
bd2.C
Dim dt2 As fxDataSource
bd2.AddTable("临时医嘱","住次ID","住院主表","住次ID")
bd2.Groups.AddDef("住次ID") \'根据品名分组
bd2.Groups.AddDef("姓名") \'根据品名分组
bd2.Groups.AddDef("参保方式") \'根据品名分组
bd2.Groups.AddDef("送报日期") \'根据品名分组
bd2.Groups.AddDef("项目ID") \'根据品名分组
bd2.Groups.AddDef("收费项目") \'根据品名分组
bd2.Groups.AddDef("项目类别") \'根据型号分组
bd2.Groups.AddDef("项目规格") \'根据品名分组
bd2.Groups.AddDef("单位") \'根据型号分组
bd2.Groups.AddDef("单价") \'根据型号分组
bd2.Totals.AddExp("临时医嘱_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd2.Totals.AddExp("临时医嘱_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt2 = bd2.BuildDataSource()

Dim bd3 As New SQLGroupTableBuilder("统计表3","实验室记录")
bd3.C
Dim dt3 As fxDataSource
bd3.AddTable("实验室记录","住次ID","住院主表","住次ID")
bd3.Groups.AddDef("住次ID") \'根据品名分组
bd3.Groups.AddDef("姓名") \'根据品名分组
bd3.Groups.AddDef("参保方式") \'根据品名分组
bd3.Groups.AddDef("送报日期") \'根据品名分组
bd3.Groups.AddDef("项目ID") \'根据品名分组
bd3.Groups.AddDef("收费项目") \'根据品名分组
bd3.Groups.AddDef("项目类别") \'根据型号分组
bd3.Groups.AddDef("项目规格") \'根据品名分组
bd3.Groups.AddDef("单位") \'根据型号分组
bd3.Groups.AddDef("单价") \'根据型号分组
bd3.Totals.AddExp("实验室记录_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd3.Totals.AddExp("实验室记录_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt3 = bd3.BuildDataSource()

Dim bd4 As New SQLGroupTableBuilder("统计表4","行为冲动")
bd4.C
Dim dt4 As fxDataSource
bd4.AddTable("行为冲动","住次ID","住院主表","住次ID")
bd4.Groups.AddDef("参保方式") \'根据品名分组
bd4.Groups.AddDef("送报日期") \'根据品名分组
bd4.Groups.AddDef("住次ID") \'根据品名分组
bd4.Groups.AddDef("姓名") \'根据品名分组
bd4.Groups.AddDef("参保方式") \'根据品名分组
bd4.Groups.AddDef("送报日期") \'根据品名分组
bd4.Groups.AddDef("项目ID") \'根据品名分组
bd4.Groups.AddDef("收费项目") \'根据品名分组
bd4.Groups.AddDef("项目类别") \'根据型号分组
bd4.Groups.AddDef("项目规格") \'根据品名分组
bd4.Groups.AddDef("单位") \'根据型号分组
bd4.Groups.AddDef("单价") \'根据型号分组
bd4.Totals.AddExp("行为冲动_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd4.Totals.AddExp("行为冲动_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt4 = bd4.BuildDataSource()

Dim bd5 As New SQLGroupTableBuilder("统计表5","行为矫正")
bd5.C
Dim dt5 As fxDataSource
bd5.AddTable("行为矫正","住次ID","住院主表","住次ID")
bd5.Groups.AddDef("住次ID") \'根据品名分组
bd5.Groups.AddDef("姓名") \'根据品名分组
bd5.Groups.AddDef("参保方式") \'根据品名分组
bd5.Groups.AddDef("送报日期") \'根据品名分组
bd5.Groups.AddDef("项目ID") \'根据品名分组
bd5.Groups.AddDef("收费项目") \'根据品名分组
bd5.Groups.AddDef("项目类别") \'根据型号分组
bd5.Groups.AddDef("项目规格") \'根据品名分组
bd5.Groups.AddDef("单位") \'根据型号分组
bd5.Groups.AddDef("单价") \'根据型号分组
bd5.Totals.AddExp("行为矫正_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd5.Totals.AddExp("行为矫正_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt5 = bd5.BuildDataSource()

Dim bd6 As New SQLGroupTableBuilder("统计表6","行为观察")
bd6.C
Dim dt6 As fxDataSource
bd6.AddTable("行为观察","住次ID","住院主表","住次ID")
bd6.Groups.AddDef("住次ID") \'根据品名分组
bd6.Groups.AddDef("姓名") \'根据品名分组
bd6.Groups.AddDef("参保方式") \'根据品名分组
bd6.Groups.AddDef("送报日期") \'根据品名分组
bd6.Groups.AddDef("项目ID") \'根据品名分组
bd6.Groups.AddDef("收费项目") \'根据品名分组
bd6.Groups.AddDef("项目类别") \'根据型号分组
bd6.Groups.AddDef("项目规格") \'根据品名分组
bd6.Groups.AddDef("单位") \'根据型号分组
bd6.Groups.AddDef("单价") \'根据型号分组
bd6.Totals.AddExp("行为观察_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd6.Totals.AddExp("行为观察_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt6 = bd6.BuildDataSource()

Dim bd7 As New SQLGroupTableBuilder("统计表7","量表首诊")
bd7.C
Dim dt7 As fxDataSource
bd7.AddTable("量表首诊","住次ID","住院主表","住次ID")
bd7.Groups.AddDef("住次ID") \'根据品名分组
bd7.Groups.AddDef("姓名") \'根据品名分组
bd7.Groups.AddDef("参保方式") \'根据品名分组
bd7.Groups.AddDef("送报日期") \'根据品名分组
bd7.Groups.AddDef("项目ID") \'根据品名分组
bd7.Groups.AddDef("收费项目") \'根据品名分组
bd7.Groups.AddDef("项目类别") \'根据型号分组
bd7.Groups.AddDef("项目规格") \'根据品名分组
bd7.Groups.AddDef("单位") \'根据型号分组
bd7.Groups.AddDef("单价") \'根据型号分组
bd7.Totals.AddExp("量表首诊_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd7.Totals.AddExp("量表首诊_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt7 = bd7.BuildDataSource()

Dim bd8 As New SQLGroupTableBuilder("统计表8","量表护士观察")
bd8.C
Dim dt8 As fxDataSource
bd8.AddTable("量表护士观察","住次ID","住院主表","住次ID")
bd8.Groups.AddDef("住次ID") \'根据品名分组
bd8.Groups.AddDef("姓名") \'根据品名分组
bd8.Groups.AddDef("参保方式") \'根据品名分组
bd8.Groups.AddDef("送报日期") \'根据品名分组
bd8.Groups.AddDef("项目ID") \'根据品名分组
bd8.Groups.AddDef("收费项目") \'根据品名分组
bd8.Groups.AddDef("项目类别") \'根据型号分组
bd8.Groups.AddDef("项目规格") \'根据品名分组
bd8.Groups.AddDef("单位") \'根据型号分组
bd8.Groups.AddDef("单价") \'根据型号分组
bd8.Totals.AddExp("量表护士观察_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd8.Totals.AddExp("量表护士观察_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt8 = bd8.BuildDataSource()

Dim bd9 As New SQLGroupTableBuilder("统计表9","量表TESS")
bd9.C
Dim dt9 As fxDataSource
bd9.AddTable("量表TESS","住次ID","住院主表","住次ID")
bd9.Groups.AddDef("住次ID") \'根据品名分组
bd9.Groups.AddDef("姓名") \'根据品名分组
bd9.Groups.AddDef("参保方式") \'根据品名分组
bd9.Groups.AddDef("送报日期") \'根据品名分组
bd9.Groups.AddDef("项目ID") \'根据品名分组
bd9.Groups.AddDef("收费项目") \'根据品名分组
bd9.Groups.AddDef("项目类别") \'根据型号分组
bd9.Groups.AddDef("项目规格") \'根据品名分组
bd9.Groups.AddDef("单位") \'根据型号分组
bd9.Groups.AddDef("单价") \'根据型号分组
bd9.Totals.AddExp("量表TESS_报数","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量],0)") \'对数量进行统计
bd9.Totals.AddExp("量表TESS_金额","IIF([开始日期] >= [报起始日期]And[开始日期] <= [报截止日期],[数量] * [单价],0)") \'对数量进行统计
dt9 = bd9.BuildDataSource()

Dim nms As String() = {"住次ID","姓名","参保方式","送报日期","项目ID","收费项目","项目类别","项目规格","单位","单价"}
dt1.Combine(nms,dt2,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt3,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt4,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt5,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt6,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt7,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt8,nms)\'将销售统计数据组合到进货统计数据
dt1.Combine(nms,dt9,nms)\'将销售统计数据组合到进货统计数据
dt1.Show("报销清单") \'显示统计结果

With DataTables("报销清单").DataCols \'用表达式列计算库存数据
    .Add("数量",Gettype(Integer), "IsNull([长期医嘱_报数],0) + ISNULL([临时医嘱_报数],0)")
    .Add("金额",Gettype(Double), "IsNull([长期医嘱_金额],0) + ISNULL([临时医嘱_金额],0)")
MainTable = Tables("报销清单") \'打开生成的统计表
End With
Tables("报销清单").OpenView("报销清单")

Dim Cols1() As String = {"住次ID","姓名","参保方式","送报日期","项目ID","收费项目","项目类别","项目规格","单位","单价","长期医嘱_报数","长期医嘱_金额","临时医嘱_报数","临时医嘱_金额","实验室记录_报数","实验室记录_金额","行为冲动_报数","行为冲动_金额","行为矫正_报数","行为矫正_金额","行为观察_报数","行为观察_金额","量表首诊_报数","量表首诊_金额","量表护士观察_报数","量表护士观察_金额","量表TESS_报数","量表TESS_金额","数量","金额"}
Dim Cols2() As String = {"住次ID","姓名","参保方式","送报日期","项目ID","收费项目","项目类别","项目规格","单位","单价","长期医嘱_报数","长期医嘱_金额","临时医嘱_报数","临时医嘱_金额","实验室记录_报数","实验室记录_金额","行为冲动_报数","行为冲动_金额","行为矫正_报数","行为矫正_金额","行为观察_报数","行为观察_金额","量表首诊_报数","量表首诊_金额","量表护士观察_报数","量表护士观察_金额","量表TESS_报数","量表TESS_金额","数量","金额"}
For Each dr1 As DataRow In DataTables("报销清单").Select("[金额] > 0")
    Dim dr2 As DataRow = DataTables("动态报次清单").AddNew()
    For i As Integer = 0 To Cols1.Length -1
        dr2(Cols2(i)) = dr1(Cols1(i))
    Next
Next

Tables("动态报次清单").OpenView("动态报次清单")
下载信息  [文件大小：   下载次数： ]
点击浏览该文件:za20120720.zip

--  作者：jk362223
--  发布时间：2012/7/28 22:02:00
--

找出错的原因了，，是列名的原因，因来至多表，所以要指明列来自哪个表，{"表名","列名"},这样改了,就可以了,,