当行在不同的列上匹配时，SQL 查询返回列的最大值

Question

我有一个包含测试表、问题表和响应表的数据集。有些问题可以由同一用户多次回答。每次用户多次回答同一个问题时，他们回答的顺序将存储为答案的排序顺序。

我正在尝试编写一个查询，该查询将针对给定测试 ID 的每个答案返回我想要的信息，以及用户回答每个问题的次数。我将使用该数字和排序顺序来特定“回答第 n 个，共 3 个”，其中 n 是排序顺序编号，3 是用户回答该问题的次数。到目前为止，我没有给出回答次数的 sql 是:

    select q.id as question_id
    ,r."textValue" as text_value, r.id, r.id as response_id
    ,q."internalQuestion" as "is_internal_question"
    ,q."requestExplanation" as "question_text"
    ,r."subResponseOrder" as "subOrder" 
    from response r
    join question q on (r.question_id = q.id)
    where r.test_id = 12345

如何修改此查询以同时为我提供用户提交的每个问题的最大子顺序？如 用户 3 回答问题 #3 四次和问题 #2 两次。数字应该在每个答案上。换句话说，每个用户 2 对问题 3 的回答都有一个“number_of_responses”数字 4。与子订单配对，我可以说答案是 4 中的 1、4 中的 2，等等……我在想将是一个子查询或一个最大值，但我无法使最大值起作用，而且我不知道实现它的最佳方法。

Answer 1

如果我没理解错的话，你可以使用窗口函数:

select q.id as question_id,
       r."textValue" as text_value, r.id, r.id as response_id,
       q."internalQuestion" as "is_internal_question",
       q."requestExplanation" as "question_text",
       r."subResponseOrder" as "subOrder",
       max(r."subResponseOrder") over (partition by q.id) as max_subResponseOrder
from response r join
     question q
     on r.question_id = q.id
where r.test_id = 12345