SQLite计算测试耗时中位数时出现'mer.answer_id'列不存在错误求助

错误原因 #

你碰到的no such column: mer.answer_id是因为SQLite的CTE（公共表表达式）作用域仅限自身所在的子查询内部，没法直接引用外层查询的mer表列，这就是报错的核心原因。

方案一：用窗口函数实现（推荐，SQLite 3.25+支持） #

利用窗口函数按answer_id分组排序，能高效计算每个题目的中位数，同时避开CTE的作用域问题：

WITH converted_times AS (
    SELECT
        mer.answer_id,
        -- 把时分秒格式转成总秒数
        CAST(substr(mer.time_spent, 1, 2) AS INTEGER) * 3600 +
        CAST(substr(mer.time_spent, 4, 2) AS INTEGER) * 60 +
        CAST(substr(mer.time_spent, 7, 2) AS INTEGER) AS seconds,
        -- 按answer_id分组，给耗时排序生成行号
        ROW_NUMBER() OVER (PARTITION BY mer.answer_id ORDER BY 
            CAST(substr(mer.time_spent, 1, 2) AS INTEGER) * 3600 +
            CAST(substr(mer.time_spent, 4, 2) AS INTEGER) * 60 +
            CAST(substr(mer.time_spent, 7, 2) AS INTEGER)
        ) AS row_num,
        -- 统计每个answer_id分组的总记录数
        COUNT(*) OVER (PARTITION BY mer.answer_id) AS total_rows
    FROM mock_exam_results AS mer
    JOIN answers AS a ON mer.answer_id = a.id
    WHERE mer.is_first_time = 1
      AND a.is_correct = 1
),
median_values AS (
    SELECT
        answer_id,
        -- 奇数取中间行，偶数取中间两行的平均值
        AVG(seconds) AS median_time_seconds
    FROM converted_times
    WHERE row_num IN (
        FLOOR((total_rows + 1) / 2),
        FLOOR((total_rows + 2) / 2)
    )
    GROUP BY answer_id
)
-- 关联原表和中位数结果，输出每条记录对应的题目中位数耗时
SELECT
    mer.*,
    printf('%02d:%02d:%02d',
        FLOOR(mv.median_time_seconds / 3600),
        FLOOR((mv.median_time_seconds % 3600) / 60),
        FLOOR(mv.median_time_seconds % 60)
    ) AS users_median_time_spent
FROM mock_exam_results AS mer
LEFT JOIN median_values AS mv ON mer.answer_id = mv.answer_id;

方案二：兼容旧版SQLite（无窗口函数） #

如果你的SQLite版本低于3.25，不支持窗口函数，可改用关联子查询的方式（效率稍低）：

SELECT
    mer.*,
    printf('%02d:%02d:%02d',
        FLOOR(median_time_seconds / 3600),
        FLOOR((median_time_seconds % 3600) / 60),
        FLOOR(median_time_seconds % 60)
    ) AS users_median_time_spent
FROM mock_exam_results AS mer
LEFT JOIN (
    SELECT
        mer_inner.answer_id,
        AVG(
            CAST(substr(mer_inner.time_spent, 1, 2) AS INTEGER) * 3600 +
            CAST(substr(mer_inner.time_spent, 4, 2) AS INTEGER) * 60 +
            CAST(substr(mer_inner.time_spent, 7, 2) AS INTEGER)
        ) AS median_time_seconds
    FROM mock_exam_results AS mer_inner
    JOIN answers AS a ON mer_inner.answer_id = a.id
    WHERE mer_inner.is_first_time = 1
      AND a.is_correct = 1
    GROUP BY mer_inner.answer_id
    HAVING (
        -- 统计当前行在分组中小于等于它的记录数，需大于等于中位数位置
        SELECT COUNT(*) 
        FROM mock_exam_results AS mer_sub
        JOIN answers AS a_sub ON mer_sub.answer_id = a_sub.id
        WHERE mer_sub.is_first_time = 1
          AND a_sub.is_correct = 1
          AND mer_sub.answer_id = mer_inner.answer_id
          AND (
              CAST(substr(mer_sub.time_spent, 1, 2) AS INTEGER) * 3600 +
              CAST(substr(mer_sub.time_spent, 4, 2) AS INTEGER) * 60 +
              CAST(substr(mer_sub.time_spent, 7, 2) AS INTEGER)
          ) <= (
              CAST(substr(mer_inner.time_spent, 1, 2) AS INTEGER) * 3600 +
              CAST(substr(mer_inner.time_spent, 4, 2) AS INTEGER) * 60 +
              CAST(substr(mer_inner.time_spent, 7, 2) AS INTEGER)
          )
    ) >= (SELECT (COUNT(*) + 1) / 2 
          FROM mock_exam_results AS mer_sub2
          JOIN answers AS a_sub2 ON mer_sub2.answer_id = a_sub2.id
          WHERE mer_sub2.is_first_time = 1
            AND a_sub2.is_correct = 1
            AND mer_sub2.answer_id = mer_inner.answer_id)
    AND (
        -- 统计当前行在分组中大于等于它的记录数，需大于等于中位数位置
        SELECT COUNT(*) 
        FROM mock_exam_results AS mer_sub3
        JOIN answers AS a_sub3 ON mer_sub3.answer_id = a_sub3.id
        WHERE mer_sub3.is_first_time = 1
          AND a_sub3.is_correct = 1
          AND mer_sub3.answer_id = mer_inner.answer_id
          AND (
              CAST(substr(mer_sub3.time_spent, 1, 2) AS INTEGER) * 3600 +
              CAST(substr(mer_sub3.time_spent, 4, 2) AS INTEGER) * 60 +
              CAST(substr(mer_sub3.time_spent, 7, 2) AS INTEGER)
          ) >= (
              CAST(substr(mer_inner.time_spent, 1, 2) AS INTEGER) * 3600 +
              CAST(substr(mer_inner.time_spent, 4, 2) AS INTEGER) * 60 +
              CAST(substr(mer_inner.time_spent, 7, 2) AS INTEGER)
          )
    ) >= (SELECT (COUNT(*) + 1) / 2 
          FROM mock_exam_results AS mer_sub4
          JOIN answers AS a_sub4 ON mer_sub4.answer_id = a_sub4.id
          WHERE mer_sub4.is_first_time = 1
            AND a_sub4.is_correct = 1
            AND mer_sub4.answer_id = mer_inner.answer_id)
) AS median_data ON mer.answer_id = median_data.answer_id;

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