MS Access无CTE/rownumber下实现同单元居民ID行转列查询方案问询

核心思路：Access 不支持窗口函数的前提下，先通过关联子查询/DCount 域函数为同楼栋、同单元的居民生成1、2、3的序列编号，再通过条件聚合/交叉表查询实现行转列。

步骤1：创建带居民序号的中间查询 #

先新建查询，命名为Query_ResSeq，可选择以下两种实现逻辑之一：

关联子查询实现（逻辑通用） #

SELECT 
  Q1.BldID,
  Q1.UnitID,
  Q1.ResidentID,
  (SELECT COUNT(*) 
   FROM Query1 AS Q2 
   WHERE Q2.BldID = Q1.BldID 
     AND Q2.UnitID = Q1.UnitID 
     AND Q2.ResidentID <= Q1.ResidentID) AS ResSeq
FROM Query1 AS Q1;

DCount 函数实现（大数据量下效率更高） #

SELECT 
  BldID,
  UnitID,
  ResidentID,
  DCount("ResidentID", "Query1", "BldID=" & [BldID] & " AND UnitID=" & [UnitID] & " AND ResidentID<='" & Replace([ResidentID], "'", "''") & "'") AS ResSeq
FROM Query1;

步骤2：聚合生成最终结果 #

再新建查询，基于Query_ResSeq做条件聚合，SQL代码如下：

SELECT 
  BldID,
  UnitID,
  MAX(IIF(ResSeq=1, ResidentID, NULL)) AS Res1,
  MAX(IIF(ResSeq=2, ResidentID, NULL)) AS Res2,
  MAX(IIF(ResSeq=3, ResidentID, NULL)) AS Res3
FROM Query_ResSeq
GROUP BY BldID, UnitID;

如果单个单元最多有超过3个居民，按需新增对应ResSeq序号的IIF列即可。

快捷实现方案：使用Access交叉表向导 #

也可以直接用Access自带的可视化功能完成：

• 打开查询设计界面，添加Query_ResSeq
• 选择「交叉表查询」类型
• 行标题勾选BldID、UnitID两个字段
• 列标题勾选ResSeq字段
• 值选择ResidentID字段，聚合方式选「第一条」或「最后一条」
• 直接执行即可得到目标结果

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