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

以文本方式查看主题

-  Foxtable(狐表)  (http://foxtable.net/bbs/index.asp)
--  专家坐堂  (http://foxtable.net/bbs/list.asp?boardid=2)
----  组合多个统计结果问题  (http://foxtable.net/bbs/dispbbs.asp?boardid=2&id=87712)

--  作者：fubblyc
--  发布时间：2016/7/18 9:27:00
--  组合多个统计结果问题

狐爸辛苦了。一大早回复这么多问题！！

昨天遇到了一个问题，研究了一个晚上没弄出来。。。

此主题相关图片如下：5.jpg

就是为什么上图的356行之后，就只显示一个统计表的数据，没有把另一个的统计表通过会员卡号给组合进来

代码如下：

\'下面的代码是搜索条件，可略过，直接看下面标黄色的统计代码

第一个统计表的搜索条件

Dim Filter As String

With e.Form.Controls("DateTimePicker1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "开卡日期 >= \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("DateTimePicker2")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "开卡日期 <= \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("checkedComboBox5")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "门店名称 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("checkedComboBox4")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "出生月 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("checkedComboBox3")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "会员状态 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("checkedComboBox1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "会员类型 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("checkedComboBox2")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "会员系列 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("ComboBox1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "性别 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("TextBox1")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "姓名 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("TextBox3")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "会员卡号 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("TextBox2")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "手机 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("DateTimePicker3")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售日期 >= \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("DateTimePicker4")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "销售日期 <= \'" & .Value & "\'"
    End If
End With
\'第二个统计表的搜索条件
Dim filter2 As String

With e.Form.Controls("checkedComboBox5")
    If .Value IsNot Nothing Then
        If filter2 >"" Then
            filter2 = filter2 & " And "
        End If
        filter2 = filter2 & "门店 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("TextBox1")
    If .Value IsNot Nothing Then
        If filter2 >"" Then
            filter2 = filter2 & " And "
        End If
        filter2 = filter2 & "会员姓名 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("TextBox3")
    If .Value IsNot Nothing Then
        If filter2 >"" Then
            filter2 = filter2 & " And "
        End If
        filter2 = filter2 & "会员卡号 = \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("checkedComboBox1")
    If .Value IsNot Nothing Then
        If filter2 >"" Then
            filter2 = filter2 & " And "
        End If
        filter2 = filter2 & "会员类型 in ( \'" & .text.replace(",","\',\'") & " \') "
    End If
End With

With e.Form.Controls("DateTimePicker3")
    If .Value IsNot Nothing Then
        If Filter2 >"" Then
            Filter2 = Filter2 & " And "
        End If
        Filter2 = Filter2 & "销售日期 >= \'" & .Value & "\'"
    End If
End With

With e.Form.Controls("DateTimePicker4")
    If .Value IsNot Nothing Then
        If Filter2 >"" Then
            Filter2 = Filter2 & " And "
        End If
        Filter2 = Filter2 & "销售日期 <= \'" & .Value & "\'"
    End If
End With

\'=======================上面是搜索条件，下面是组合多个统计表的代码

Dim bd1 As New SQLgroupTableBuilder("统计表1","rfm分析2")
Dim dt1 As fxDataSource
bd1.C
bd1.Groups.AddDef("发卡单位") \'添加客户列用于水平分组
bd1.Groups.AddDef("门店名称") \'添加客户列用于水平分组
bd1.Groups.AddDef("建档日期",DateGroupEnum.none) \'添加客户列用于水平分组
bd1.Groups.AddDef("会员状态") \'添加客户列用于水平分组
bd1.Groups.AddDef("会员系列") \'添加客户列用于水平分组
bd1.Groups.AddDef("会员类型") \'添加客户列用于水平分组
bd1.Groups.AddDef("性别") \'添加客户列用于水平分组
bd1.Groups.AddDef("会员卡号") \'添加客户列用于水平分组
bd1.Groups.AddDef("姓名") \'添加客户列用于水平分组
bd1.Groups.AddDef("手机") \'添加客户列用于水平分组
bd1.Groups.AddDef("出生日期",DateGroupEnum.none) \'添加客户列用于水平分组
bd1.Groups.AddDef("当前维护人") \'添加客户列用于水平分组
bd1.Totals.AddDef("销售日期",AggregateEnum.max,"末次购买时间") \'添加客户列用于水平分组
bd1.Totals.AddDef("销售日期",AggregateEnum.count,"买过多少次") \'添加客户列用于水平分组
bd1.Totals.AddDef("销售金额","总购买金额") \'添加客户列用于水平分组
bd1.Totals.AddDef("吊牌金额","总吊牌金额") \'添加客户列用于水平分组
bd1.Filter = filter

dt1 = bd1.BuildDataSource()

Dim bd2 As New SQLcrossTableBuilder("统计表1","销售明细")
Dim dt2 As fxDataSource
bd2.C
\'bd2.HGroups.AddDef("上级名称") \'添加客户列用于水平分组
\'bd2.HGroups.AddDef("门店") \'添加客户列用于水平分组
bd2.HGroups.AddDef("会员姓名") \'添加客户列用于水平分组
bd2.HGroups.AddDef("会员类型") \'添加客户列用于水平分组
bd2.HGroups.AddDef("会员卡号") \'添加客户列用于水平分组
bd2.VGroups.AddDef("品类") \'添加日期列用于垂直分组,按月分组
bd2.VGroups.AddDef("大类") \'添加日期列用于垂直分组,按月分组
bd2.Totals.AddDef("销售数量") \'添加数量列用于统计
        If Filter2 >"" Then
            Filter2 = Filter2 & " And "
        End If
bd2.Filter = filter2 & "会员卡号 <> \'\'"
dt2 = bd2.BuildDataSource()

Dim nms1 As String() = {"会员卡号","姓名","会员类型"} \'指定连接列
Dim nms2 As String() = {"会员卡号","会员姓名","会员类型"} \'指定连接列这是两个统计表都有的字段

dt1.Combine(nms1,dt2,nms2) \'将销售统计数据组合到进货统计数据
Tables(e.Form.Name & "_table1").DataSource = dt1

[此贴子已经被作者于2016/7/18 9:29:53编辑过]

--  作者：Hyphen
--  发布时间：2016/7/18 10:25:00
--

1、第二个表统计的时候也加上这些字段
2、合并后编码查询补充这些值

--  作者：fubblyc
--  发布时间：2016/7/18 16:09:00
--

谢谢 Hyphen老师。我增加了字段。

那怎么才能补充这些值呢？通过会员卡号来查找rfm分析2的值，然后补充到第二个表中呢。。。

Dim bd2 As New SQLcrossTableBuilder("统计表2","销售明细2")
Dim dt2 As fxDataSource
bd2.C
bd2.hGroups.AddDef("发卡单位") \'添加客户列用于水平分组
bd2.hGroups.AddDef("门店名称") \'添加客户列用于水平分组
bd2.hGroups.AddDef("建档日期",DateGroupEnum.none) \'添加客户列用于水平分组
bd2.hGroups.AddDef("会员状态") \'添加客户列用于水平分组
bd2.hGroups.AddDef("会员系列") \'添加客户列用于水平分组
bd2.hGroups.AddDef("会员类型") \'添加客户列用于水平分组
bd2.hGroups.AddDef("性别") \'添加客户列用于水平分组
bd2.HGroups.AddDef("姓名") \'添加客户列用于水平分组
bd2.HGroups.AddDef("会员卡号") \'添加客户列用于水平分组
bd2.hGroups.AddDef("手机") \'添加客户列用于水平分组
bd2.hGroups.AddDef("出生日期",DateGroupEnum.none) \'添加客户列用于水平分组
bd2.hGroups.AddDef("当前维护人") \'添加客户列用于水平分组
bd2.VGroups.AddDef("品类") \'添加日期列用于垂直分组,按月分组
bd2.VGroups.AddDef("大类") \'添加日期列用于垂直分组,按月分组
bd2.Totals.AddDef("销售数量") \'添加数量列用于统计
        If Filter2 >"" Then
            Filter2 = Filter2 & " And "
        End If
bd2.Filter = filter2 & "会员卡号 <> \'\'"
dt2 = bd2.BuildDataSource()

Dim nms1 As String() = {"会员卡号","姓名","会员类型","发卡单位","门店名称","建档日期","会员状态","会员系列","性别","手机","出生日期","当前维护人"} \'指定连接列
Dim nms2 As String() = {"会员卡号","姓名","会员类型","发卡单位","门店名称","建档日期","会员状态","会员系列","性别","手机","出生日期","当前维护人"} \'指定连接列

dt1.Combine(nms1,dt2,nms2) \'将销售统计数据组合到进货统计数据
Tables(e.Form.Name & "_table1").DataSource = dt1

--  作者：Hyphen
--  发布时间：2016/7/18 16:54:00
--
循环，然后用find查找

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

注意效率

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