如何在MS Access 2016中生成访客共同参与Workshop数量的交叉矩阵查询？

需求说明 #

• 使用MS Access 2016，现有三张表：访客表（tblVisitors）、Workshop表、参与关联表（tblAttending）
• 需要生成双向矩阵表，展示每两位访客共同参与的Workshop数量
• 此前通过VBA+复杂报表实现功能，但因用户需要支持筛选和修改操作，改用查询方案

初步尝试的无效SQL #

TRANSFORM Count(tblAttending.WorkshopID) AS CountOfWorkshopID
SELECT tblAttending.VisitorID, tblVisitors2.Name
FROM tblAttending INNER JOIN tblVisitors2 ON tblAttending.VisitorID = tblVisitors2.VisitorID
GROUP BY tblAttending.VisitorID, tblVisitors2.Name
PIVOT tblAttending.VisitorID;

最终可行的交叉表查询SQL #

TRANSFORM nz(Sum(visitingpairs.CommonWorkshop))+0 AS SumOfCommonWorkshop
SELECT visitingpairs.VisitorName1
FROM (SELECT V1.VisitorID AS VisitorID1, V1.Name AS VisitorName1, V2.VisitorID AS VisitorID2, V2.Name AS VisitorName2, Nz(Count(A.WorkshopID),0) AS CommonWorkshop
FROM (tblAttending AS A INNER JOIN tblVisitors AS V1 ON A.VisitorID = V1.VisitorID) INNER JOIN (tblAttending AS B INNER JOIN tblVisitors AS V2 ON B.VisitorID = V2.VisitorID) ON A.WorkshopID = B.WorkshopID
GROUP BY V1.VisitorID, V1.Name, V2.VisitorID, V2.Name
)  AS visitingpairs
GROUP BY visitingpairs.VisitorName1
PIVOT visitingpairs.VisitorName2;

逻辑说明 #

1. 子查询visitingpairs：通过两次关联tblAttending表，匹配参与同一Workshop的访客对，统计每对访客共同参与的Workshop数量
2. 外层通过TRANSFORM+PIVOT将访客对转换为双向矩阵，行和列均为访客姓名，单元格值为共同参与的Workshop数量
3. 使用Nz()函数处理空值，确保无共同参与记录时显示0而非空白

内容的提问来源于stack exchange，提问作者Vickie Mac