MySQL查询为各题目关联选项批量标注A/B/C/D，避免额外循环

需求说明 #

我正在开发问答测验功能，原实现思路为：

• 先执行一次查询拉取全部20道题目
• 遍历题目列表，每轮循环传入当前题目的ID，查询该题目下关联的所有选项

当前核心优化目标：无需逐题循环查询，即可自动为同题目下的所有选项按顺序标注A、B、C、D格式的选项序号。

选项表结构 #

id    option_value       question_id
1      some value          1
2      some value          1
3      some value          1
4      some value          1
5      some value          2
6      some value          2
7      some value          2
8      some value          2

预期输出格式 #

option_id   option_name option_value
 1           A           some value
 2           B           some value
 3           C           some value
 4           D           some value
 5           A           some value
 6           B           some value
 7           C           some value
 8           D           some value

现有代码逻辑 #

// $question_list 存储select *查询得到的全部20道题目
$question_list
foreach($question_list as $list){
    // 注意：原代码字段拼写错误，option_vaue 应为 option_value
    $this->db->select('id, option_value')->from('option')
        ->where('question_id', $list->id)
        ->order_by('id','asc')
        ->get()
        ->result();
}

实现方案 #

直接用单条SQL完成全量选项查询和序号标注，不需要提前拉取题目列表再逐题循环：

SELECT
    id AS option_id,
    CHAR(ASCII('A') + (ROW_NUMBER() OVER (PARTITION BY question_id ORDER BY id ASC) - 1)) AS option_name,
    option_value
FROM `option`
ORDER BY question_id ASC, id ASC;

• 该逻辑通过窗口函数按question_id分组，同分组内按选项ID升序生成连续行号
• 将行号映射为从A开始的大写字母，完全匹配预期的输出格式
• 一次查询即可返回所有题目对应的所有选项，彻底移除逐题循环查库的逻辑

兼容性说明：若使用不支持窗口函数的低版本数据库（如MySQL 5.x），可通过自定义会话变量实现分组排序的等价效果。

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