主题：两张表里面查数据

您好，我想在同一个数据库的两张表里面查数据，并把查到的数据组到一个张临时统计表里面？可以给例子参考么？

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

 

还是这个？

 

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

 

1：

基本信息表{张三 ，李四 ，王五}

历史库{}

 

2：

发生事件：张三2018年2月份死亡

基本信息表{李四 ，王五}

历史库{张三}

 

 

3：

 

统计需求：2018年7月18日要统计2017年年底基本信息表里面有多少人？

 

应当是 {张三、李四、王五}共3人

 

 

请教老师，这个该如何实现呀？

 

union 即可。如果写sql语句就是

 

select 姓名,性别 from {基础信息表} union all

select 姓名,性别 from {历史库}

Dim jb1 As New SQLJoinTableBuilder("查询表1","person")
jb1.C
jb1.AddTable("person","retireTime","history","retireTime")
jb1.AddCols("{person}.name","{person}.sex","{person}.organName", "{person}.retireTime")
jb1.Filter = "{person}.retireTime = '2017-07-01'"

Dim jb2 As New SQLJoinTableBuilder("查询表1","history")
jb2.C
jb1.AddTable("history","retireTime","person","retireTime")
jb1.AddCols("{history}.name","{history}.sex","{history}.organName", "{history}.retireTime")
jb1.Filter = "{history}.retireTime = '2017-07-01'"

jb1.Union(jb2) '组合jb2
jb1.Build()
MainTable = Tables("查询表1")

 

这个代码有错，麻烦帮我看看

参考代码

 

Dim jb1 As New SQLJoinTableBuilder("查询表1","表A")
jb1.AddCols("第一列", "第二列")

Dim jb2 As New SQLJoinTableBuilder("查询表2","表B")
jb2.AddCols("第一列", "第二列")

jb1.Union(jb2)

jb1.Build

此主题相关图片如下：1.png

 

1、红色字段可以根据用户输入么？

2、蓝色条件可以是用户输入么？

3、合并的这两个结果集可以生成一个统计表么？

如果可以，以上如何实现？

 

谢谢您了

看6楼代码吧，sql语句比较麻烦，看懂

 

Dim jb1 As New SQLJoinTableBuilder("查询表1","表A")
jb1.AddCols("第一列", "第二列")

jb1.filter = "第一列='123'"

Dim jb2 As New SQLJoinTableBuilder("查询表2","表B")
jb2.AddCols("第一列", "第二列")

jb1.filter = "第一列='abc'"

jb1.Union(jb2)

jb1.Build

 

如果你需要用户输入，参考下面代码

 

Dim cs1 As String = "第一列,第二列"
Dim cs2 As String = "第一列,第二列"
Dim f1 As String = "第一列='123'"
Dim f2 As String = "第一列='abc'"

Dim jb1 As New SQLJoinTableBuilder("查询表1","表A")
For Each s As String In cs1.split(",")
    jb1.AddExp(s, s)
Next
jb1.filter = f1

Dim jb2 As New SQLJoinTableBuilder("查询表2","表B")
For Each s As String In cs1.split(",")
    jb2.AddExp(s, s)
Next
jb1.filter = f2
jb1.Union(jb2)

jb1.Build

Dim Filter As String
With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        Filter = Filter & "{person}.retireTime >= '" & .Value & "'"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "{person}.retireTime<= '" & .Value & "'"
    End If
End With

''Dim cs As String = "第一列,第二列"
'
Dim jb1 As New SQLJoinTableBuilder("查询表1","person")
jb1.C
jb1.AddTable("person","retireTime","history","retireTime")
jb1.AddCols("{person}.name", "{person}.sex")
''For Each s As String In cs1.split(",")
    ''jb1.AddExp(s, s)
''Next
If Filter > "" Then
    jb1.Filter = Filter
End If
'
'
Dim jb2 As New SQLJoinTableBuilder("查询表1","history")
jb2.C
jb2.AddTable("history","retireTime","person","retireTime")
jb2.AddCols("{history}.name", "{history}.sex")
''For Each s As String In cs1.split(",")
    ''jb2.AddExp(s, s)
''Next
If Filter > "" Then
    jb2.Filter = Filter
End If
'
jb1.Union(jb2)
jb1.Build

 

 

 

1、filter无效，选了时间段，但是筛选不出来

 

Dim Filter As String = ""
With e.Form.Controls("StartDate")
    If .Value IsNot Nothing Then
        Filter = Filter & "retireTime >= '" & .Value & "'"
    End If
End With
With e.Form.Controls("EndDate")
    If .Value IsNot Nothing Then
        If Filter >"" Then
            Filter = Filter & " And "
        End If
        Filter = Filter & "retireTime<= '" & .Value & "'"
    End If
End With

 

Dim jb1 As New SQLJoinTableBuilder("查询表1","person")
jb1.AddCols("name", "sex")

jb1.Filter = Filter

Dim jb2 As New SQLJoinTableBuilder("查询表2","history")
jb2.AddCols("name", "sex")

jb2.Filter = Filter

jb1.Union(jb2)
jb1.Build