SQL Server 2012中高效查询指定问题及用户前置答题记录的方案咨询

嘿，很高兴帮你优化这个SQL查询！用游标确实能实现需求，但面对大量数据时效率会很低——毕竟游标是逐行处理，而SQL天生擅长集合操作，用窗口函数就能高效解决这个问题，完全不需要游标。

需求回顾 #

我们需要提取两类记录：

1. 所有用户回答IdQuestion = 5的记录
2. 每个用户回答该问题之前，最后提交的那条答题记录

高效实现方案（SQL Server 2012+） #

SQL Server 2012支持ROW_NUMBER()窗口函数，我们可以用它给每个用户的答题记录按提交顺序（假设IdAnswer自增代表提交先后，若有时间列可替换）编号，再精准筛选目标记录：

WITH UserAnswerSequence AS (
    SELECT 
        IdAnswer,
        IdUser,
        IdQuestion,
        Answer,
        -- 给每个用户的答题记录按提交顺序编号（1=最早，N=最晚）
        ROW_NUMBER() OVER (PARTITION BY IdUser ORDER BY IdAnswer ASC) AS AnswerSeq,
        -- 标记当前记录是否是目标问题
        CASE WHEN IdQuestion = 5 THEN 1 ELSE 0 END AS IsTarget
    FROM YourAnswerTable -- 替换成你的实际表名
),
TargetAnswerInfo AS (
    SELECT 
        IdUser,
        -- 获取每个用户回答目标问题的记录编号
        MAX(CASE WHEN IsTarget = 1 THEN AnswerSeq END) AS TargetSeq
    FROM UserAnswerSequence
    GROUP BY IdUser
    -- 只保留回答过目标问题的用户
    HAVING MAX(IsTarget) = 1
)
SELECT 
    uas.IdAnswer,
    uas.IdUser,
    uas.IdQuestion,
    uas.Answer
FROM UserAnswerSequence uas
JOIN TargetAnswerInfo tai ON uas.IdUser = tai.IdUser
WHERE 
    -- 筛选目标问题记录，或它的上一条记录
    uas.AnswerSeq = tai.TargetSeq 
    OR uas.AnswerSeq = tai.TargetSeq - 1
ORDER BY uas.IdUser, uas.AnswerSeq;

代码解释 #

1. 第一个CTE UserAnswerSequence：给每个用户的答题记录排序编号，同时标记哪些是目标问题的记录。
2. 第二个CTE TargetAnswerInfo：找出每个用户回答目标问题时的记录编号，只保留有过目标问题回答的用户。
3. 最终查询：关联两个CTE，筛选出每个用户的目标问题记录，以及它的前一条记录。

为什么比游标高效？ #

• 这是集合式操作，SQL Server查询优化器可以生成更优的执行计划（比如利用索引快速排序、分组）。
• 游标是逐行遍历处理，数据量越大，性能差距越明显——窗口函数的时间复杂度是O(n log n)，游标则是O(n)但常数项极大。

注意事项 #

• 如果你的表有专门的答题时间列（比如SubmitTime），把ORDER BY IdAnswer ASC换成ORDER BY SubmitTime ASC即可，更贴合实际业务逻辑。
• 如果某个用户回答目标问题是他的第一条答题记录，TargetSeq - 1会等于0，此时只会返回目标问题记录，符合逻辑（没有更早的记录）。

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