Part 1:目标
成绩表
学生信息表
运行过程
- 获取学号为1101学生在不同年级的语文成绩,输出信息包括:姓名,学号,性别,年级,语文成绩
- 最终想要获得的信息来自于两个工作表,所以需要连接查询
- 逻辑过程
- 连接数据库
- 根据需求确定SQL语句
- 执行SQL语句,得到recordset
- 将recordset写入工作表(字段名+所有记录 即 列名+每一行)
- 断开与数据库的连接
- SQL语句
- "Select " & opFilds & " from " & "(" & tbls & ")" & " where (" & searchC & ")"
- opFilds = "姓名," & tblNote & ".学号,性别,年级,语文成绩"
- searchC = tblNote & ".学号=1101"
- tbls = tblNote & " left join " & tblName & " on " & tblNote & ".学号=" & tblName & ".学号"
Part 2:代码
Sub test() Dim cnn As New ADODB.Connection '连接 Dim rs As New ADODB.Recordset Dim SQL As String Dim tblName Dim dbAddr dbAddr = ThisWorkbook.Path & "学生信息.accdb" tblName = "学生信息表" tblNote = "成绩表" '连接数据库 With cnn .Provider = "Microsoft.ACE.OLEDB.12.0" .Open "Data Source=" & dbAddr End With opFilds = "姓名," & tblNote & ".学号,性别,年级,语文成绩" searchC = tblNote & ".学号=1101" tbls = tblNote & " left join " & tblName _ & " on " & tblNote & ".学号=" & tblName & ".学号" SQL = "Select " & opFilds & " from " & "(" & tbls & ")" & " where (" & searchC & ")" Set rs = cnn.Execute(SQL) Dim sht Dim fildNum Set sht = ThisWorkbook.Worksheets("示例") sht.Cells.ClearContents fildNum = rs.Fields.Count For j = 0 To fildNum - 1 Step 1 fildName = rs.Fields(j).Name sht.Cells(1, j + 1) = fildName Next j sht.Cells(2, 1).CopyFromRecordset rs cnn.Close Set rs = Nothing Set cnn = NothingEnd Sub
代码截图
执行结果
Part 3:部分代码解读
- SQL = "Select " & opFilds & " from " & "(" & tbls & ")" & " where (" & searchC & ")"
- 本问题中,相关变量取值后SQL语句如下
- Select 姓名,成绩表.学号,性别,年级,语文成绩 from (成绩表 left join 学生信息表 on 成绩表.学号=学生信息表.学号) where (成绩表.学号=1101)
Select 姓名,成绩表.学号,性别,年级,语文成绩 from (成绩表 left join 学生信息表 on 成绩表.学号=学生信息表.学号) where (成绩表.学号=1101)
- 中文解读:从成绩表和学生信息表连接表中获取学号为1101的学生信息
- 表1 left join 表2 on 表1.ID=表2.ID
- 两个表进行连接,以左侧为基准,即这里的表1
- 连接条件:表1与表2的ID号相同
- 当表2中满足表1中ID条件的有多条记录,那么进行分别匹配
- 当表2中没有满足表1中ID的条件时,匹配Null
例1:匹配多个,修改学生信息表,增加同样学号的
查询结果
例2:若未匹配上,则对应信息为空