数据库一: test1.mdb
其中的表一:table1_1
字段: no name age
100 zhang 24
104 li 30
109 wang 25
..........
336 gu 20
999 zhao 30
数据库二: test2.mdb
其中的表一:table2_1
字段: no name age
100 zhang 24
109 wang 25
..........
898 san 33
999 zhao 30
SQL功能要求:两张数据表通过no属性字段为关键字进行查询,查询出table1_1表和table2_1表*同存在的所有记录(即交集)
13 个解决方案
#1
select * From 表1 a ,表2 b where a.no=b.no
#2
两个Access数据库进行跨库查询?
你还想用单一的SQL语句实现?
据我所知行不通
你还想用单一的SQL语句实现?
据我所知行不通
#3
多条SQL也行。给个方法吗。
#4
用存储过程试试看。
先分别搜索出两个数据库的数据放到临时表。然后再把两个临时表连接。。应该可以的。
先分别搜索出两个数据库的数据放到临时表。然后再把两个临时表连接。。应该可以的。
#5
select * from test1.table1_1 a,test2.dbo.table2_1 b where a.no=b.no 这样也可以。
#6
要把test1,或test2 去掉一个。。。看你连接的数据库是哪个。
#7
you'd better use the third file to swipe it
#8
在同一服务器上mssql数据库,连接其中之一:
select *
from db1.dbo.tb a inner join db2.dbo.tb b
on a.no=b.no
access,连接其中db1:
select *
from tb a inner join [d:\db2.mdb;pwd=123].tb b
on a.no=b.no
select *
from db1.dbo.tb a inner join db2.dbo.tb b
on a.no=b.no
access,连接其中db1:
select *
from tb a inner join [d:\db2.mdb;pwd=123].tb b
on a.no=b.no
#9
我是要在VB中通过DAO对象的Execute方式执行SQL语句实现以上操作,怎么做呢?
#10
用inner join
#11
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 a inner join [c:\db2.mdb;pwd=123].tb2 b on a.no=b.no"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub
#12
上面的代码实现了大部分功能,但inner join后得到的记录集包含了两个表的所有字段,如何可以不包含第二个表的字段。
#13
成功了:
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 where tb1.no in (select tb2.no from [c:\db2.mdb;pwd=123].tb2)"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 where tb1.no in (select tb2.no from [c:\db2.mdb;pwd=123].tb2)"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub
#1
select * From 表1 a ,表2 b where a.no=b.no
#2
两个Access数据库进行跨库查询?
你还想用单一的SQL语句实现?
据我所知行不通
你还想用单一的SQL语句实现?
据我所知行不通
#3
多条SQL也行。给个方法吗。
#4
用存储过程试试看。
先分别搜索出两个数据库的数据放到临时表。然后再把两个临时表连接。。应该可以的。
先分别搜索出两个数据库的数据放到临时表。然后再把两个临时表连接。。应该可以的。
#5
select * from test1.table1_1 a,test2.dbo.table2_1 b where a.no=b.no 这样也可以。
#6
要把test1,或test2 去掉一个。。。看你连接的数据库是哪个。
#7
you'd better use the third file to swipe it
#8
在同一服务器上mssql数据库,连接其中之一:
select *
from db1.dbo.tb a inner join db2.dbo.tb b
on a.no=b.no
access,连接其中db1:
select *
from tb a inner join [d:\db2.mdb;pwd=123].tb b
on a.no=b.no
select *
from db1.dbo.tb a inner join db2.dbo.tb b
on a.no=b.no
access,连接其中db1:
select *
from tb a inner join [d:\db2.mdb;pwd=123].tb b
on a.no=b.no
#9
我是要在VB中通过DAO对象的Execute方式执行SQL语句实现以上操作,怎么做呢?
#10
用inner join
#11
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 a inner join [c:\db2.mdb;pwd=123].tb2 b on a.no=b.no"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub
#12
上面的代码实现了大部分功能,但inner join后得到的记录集包含了两个表的所有字段,如何可以不包含第二个表的字段。
#13
成功了:
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 where tb1.no in (select tb2.no from [c:\db2.mdb;pwd=123].tb2)"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub
Private Sub Command1_Click()
Dim sql As String, i As Integer, j%
Dim g_ws As DAO.Workspace
Dim g_db As DAO.Database
Dim g_rs As DAO.Recordset
Set g_ws = DBEngine.Workspaces(0)
Set g_db = g_ws.OpenDatabase(App.Path + "\db1.mdb")
sql = "select * from tb1 where tb1.no in (select tb2.no from [c:\db2.mdb;pwd=123].tb2)"
Set g_rs = g_db.OpenRecordset(sql)
g_rs.MoveLast: g_rs.MoveFirst
For i = 1 To g_rs.RecordCount
For j = 0 To g_rs.Fields.Count - 1
Debug.Print g_rs.Fields(j).Value
Next j
g_rs.MoveNext
Next i
End Sub