Access自定义函数，实现递归查询
介绍
有一个数据表，表名为测试表，表结构及内容如下：
表结构
| 字段名称 | 数据类型 | 说明（可选） |
|---|---|---|
| 编号 | 数字 | 主键 |
| 父节点 | 数字 | 关联编号字段 |
| 名称 | 短文本 | |
| 类型 | 短文本 |
内容
| 编号 | 父节点 | 名称 | 类型 |
|---|---|---|---|
| 1 | 0 | 一级节点_1 | 一级节点 |
| 2 | 1 | 二级节点_1 | 二级节点 |
| 3 | 1 | 二级节点_2 | 二级节点 |
| 4 | 2 | 三级节点_1 | 三级节点 |
| 5 | 3 | 三级节点_2 | 三级节点 |
| 6 | 3 | 三级节点_3 | 三级节点 |
需要创建一个查询实现，统计每个节点下指定类型的个数，Access中没有CTE 递归查询，通过创建自定义函数实现
创建函数
在ACCESS数据库中没有存储过程、函数、触发器等功能。可以使用ACCESS提供的模块功能来实现
在数据库工具 选项卡中，点击 Visual Basic
新建一个模块。然后打开这个模块，在打开VBA环境下编写相应的代码来完成需要的功能。
DAO版
'引用 “Microsoft Office 16.0 Access database engine Object Library”
Public Function TypeCount(ID As Integer, TypeName As String) As Integer
'定义变量
Dim c As Integer
Dim rs As DAO.Recordset
'查询数据
Set rs = CurrentDb.OpenRecordset("select * from 测试表 where 父节点 = " & ID)
'循环统计
Do While Not rs.EOF
If rs("类型").Value = TypeName Then
c = c + 1
Else
c = c + TypeCount(rs("编号").Value, TypeName)
End If
rs.MoveNext
Loop
'返回结果
TypeCount = c
End Function
ADO版
'引用 “Microsoft ActiveX Data Objects 2.1 Library”
Public Function TypeCount_ADO(ID As Integer, TypeName As String) As Integer
'定义变量
Dim c As Integer
Dim rs As adodb.Recordset
'查询数据
Set rs = New adodb.Recordset
Sql = "select * from 测试表 where 父节点 = " & ID
rs.Open Sql, CurrentProject.Connection
'循环统计
Do While Not rs.EOF
If rs("类型").Value = TypeName Then
c = c + 1
Else
c = c + TypeCount_ADO(rs("编号").Value, TypeName)
End If
rs.MoveNext
Loop
'返回结果
TypeCount = c
End Function
调用函数
查询SQL
SELECT *, TypeCount(编号, '三级节点') as 包含三级节点数 FROM 测试表;
查询结果
| 包含三级节点数 | 编号 | 父节点 | 名称 | 类型 |
|---|---|---|---|---|
| 3 | 1 | 0 | 一级节点1 | 一级节点 |
| 1 | 2 | 1 | 二级节点1 | 二级节点 |
| 2 | 3 | 1 | 二级节点2 | 二级节点 |
| 0 | 4 | 2 | 三级节点1 | 三级节点 |
| 0 | 5 | 3 | 三级节点2 | 三级节点 |
| 0 | 6 | 3 | 三级节点3 | 三级节点 |