SQL关联表排序分页问题：访谈/问题/答案表需求实现

表结构 #

Create Table Interviews(
Id int primary key,
StartDate datetime2,
LocationName varchar(50),
)

Create Table Questions(
Id int primary key,
Title varchar(500)
)

Create Table Answers(
Id int primary key,
AnswerText varchar(500),
QuestionId int,
InterviewId int,
)

ALTER TABLE Answers ADD FOREIGN KEY (QuestionId) REFERENCES Questions(Id);
ALTER TABLE Answers ADD FOREIGN KEY (InterviewId) REFERENCES Interviews(Id);

表关联逻辑 #

• 面试表（Interviews）与答案表（Answers）为一对多关系，通过InterviewId字段关联
• 答案表（Answers）与问题表（Questions）为多对一关系，通过QuestionId字段关联

需求与现有问题 #

需要按姓名、年龄对面试关联数据排序并实现分页，但原有SQL直接以AnswerText整体排序，未区分姓名、年龄对应的答案，排序逻辑错误，无法得到预期结果。预期结果为：按每个面试对应的姓名、年龄排序后，分页展示前5条关联的面试、答案数据。

原有错误SQL：

SELECT *
FROM
(
    SELECT a.Id,
           a.AnswerText,
           a.QuestionId,
           a.InterviewId,
           i.LocationName,
           ROW_NUMBER() OVER (ORDER BY a.AnswerText) AS InterviewOrderNumber --排序逻辑错误
    FROM Interviews i
        LEFT JOIN Answers a
            ON i.Id = a.InterviewId
) t
WHERE InterviewOrderNumber BETWEEN 1 AND 5

解决方案 #

核心是先提取每个面试对应的姓名和年龄答案值，以此作为排序依据，再进行分页。以下提供两种实现方式：

方式1：通过问题标题匹配获取姓名、年龄 #

适用于已知问题标题包含“姓名”“年龄”关键词的场景：

SELECT *
FROM (
    SELECT 
        a.Id,
        a.AnswerText,
        a.QuestionId,
        a.InterviewId,
        i.LocationName,
        -- 提取当前面试的姓名答案
        MAX(CASE WHEN q.Title LIKE '%姓名%' THEN a_na.AnswerText END) OVER (PARTITION BY i.Id) AS CandidateName,
        -- 提取当前面试的年龄答案并转为数字，避免字符串排序错误
        CAST(MAX(CASE WHEN q.Title LIKE '%年龄%' THEN a_na.AnswerText END) OVER (PARTITION BY i.Id) AS INT) AS CandidateAge,
        -- 按姓名、年龄排序生成分页行号
        ROW_NUMBER() OVER (ORDER BY 
            MAX(CASE WHEN q.Title LIKE '%姓名%' THEN a_na.AnswerText END) OVER (PARTITION BY i.Id),
            CAST(MAX(CASE WHEN q.Title LIKE '%年龄%' THEN a_na.AnswerText END) OVER (PARTITION BY i.Id) AS INT)
        ) AS InterviewOrderNumber
    FROM Interviews i
    LEFT JOIN Answers a ON i.Id = a.InterviewId
    -- 关联获取当前面试的姓名、年龄答案
    LEFT JOIN Answers a_na ON i.Id = a_na.InterviewId
    LEFT JOIN Questions q ON a_na.QuestionId = q.Id
    WHERE q.Title IN ('姓名', '年龄') -- 可根据实际标题调整匹配规则
) t
WHERE InterviewOrderNumber BETWEEN 1 AND 5

方式2：通过已知QuestionId获取姓名、年龄 #

适用于明确知道姓名、年龄对应的QuestionId的场景（例如姓名对应ID=1，年龄对应ID=2）：

SELECT *
FROM (
    SELECT 
        a.Id,
        a.AnswerText,
        a.QuestionId,
        a.InterviewId,
        i.LocationName,
        -- 直接通过QuestionId获取姓名
        (SELECT AnswerText FROM Answers WHERE InterviewId = i.Id AND QuestionId = 1) AS CandidateName,
        -- 获取年龄并转为数字
        CAST((SELECT AnswerText FROM Answers WHERE InterviewId = i.Id AND QuestionId = 2) AS INT) AS CandidateAge,
        -- 按姓名、年龄排序生成行号
        ROW_NUMBER() OVER (ORDER BY 
            (SELECT AnswerText FROM Answers WHERE InterviewId = i.Id AND QuestionId = 1),
            CAST((SELECT AnswerText FROM Answers WHERE InterviewId = i.Id AND QuestionId = 2) AS INT)
        ) AS InterviewOrderNumber
    FROM Interviews i
    LEFT JOIN Answers a ON i.Id = a.InterviewId
) t
WHERE InterviewOrderNumber BETWEEN 1 AND 5

注意事项 #

• 年龄字段转为INT是为了避免字符串排序的逻辑错误（例如“10”不会排在“2”之后）
• 若需要切换分页页码，只需调整BETWEEN后的数值（如第2页为6-10）
• 需根据实际业务中的问题标题或QuestionId调整匹配条件

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