如何在Access查询中基于唯一question_id生成递增计数器

需求说明 #

需要在现有查询结果的末尾添加一列计数器：

• 计数器从1开始
• 同一question_id的所有行显示同一个序号
• 当question_id变化时，序号递增（例如question_id=1的所有行显示1，question_id=3的所有行显示2）

问题分析 #

你之前尝试的DCount语句未限定is_selected=True的条件，导致统计了Question表中所有question_id<=当前值的记录（包括未被选中的question_id=2），因此序号不符合预期。

解决方法 #

方法1：直接修改DCount语句 #

在现有查询中添加如下列，通过限定筛选条件，仅统计已选中的question_id的序号：

DCount("question_id", "Question", "is_selected = True AND question_id <= " & [Question.question_id]) AS QuestionCounter

完整的查询语句如下：

SELECT Question.question_id, 
       Question.question_number, 
       Paper.paper_name, 
       Exam_Year.year_date, 
       Question_image.image_filename, 
       Question_image.image_page, 
       Question.is_selected,
       DCount("question_id", "Question", "is_selected = True AND question_id <= " & [Question.question_id]) AS QuestionCounter
FROM Exam_Year INNER JOIN ((Paper INNER JOIN Question ON Paper.paper_id = Question.paper_id) INNER JOIN Question_image ON Question.question_id = Question_image.question_id) ON Exam_Year.year_id = Question.year_id
WHERE (((Question.is_selected)=True));

方法2：使用子查询关联（适合大数据量场景） #

如果数据库数据量较大，子查询的方式效率更高：

1. 先创建一个子查询（命名为qry_Question_Rank），为每个已选中的question_id分配序号：

SELECT question_id, 
       (SELECT Count(*) 
        FROM Question AS Q2 
        WHERE Q2.is_selected = True 
          AND Q2.question_id <= Q1.question_id) AS QuestionCounter
FROM Question AS Q1
WHERE Q1.is_selected = True;

2. 将主查询与该子查询关联，获取计数器列：

SELECT Question.question_id, 
       Question.question_number, 
       Paper.paper_name, 
       Exam_Year.year_date, 
       Question_image.image_filename, 
       Question_image.image_page, 
       Question.is_selected,
       qry_Question_Rank.QuestionCounter
FROM (Exam_Year INNER JOIN ((Paper INNER JOIN Question ON Paper.paper_id = Question.paper_id) INNER JOIN Question_image ON Question.question_id = Question_image.question_id) ON Exam_Year.year_id = Question.year_id)
INNER JOIN qry_Question_Rank ON Question.question_id = qry_Question_Rank.question_id
WHERE (((Question.is_selected)=True));

效果验证 #

两种方法都能实现：

• question_id=1的所有行显示QuestionCounter=1
• question_id=3的所有行显示QuestionCounter=2

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