SQL -返回数据子集的项排序

Question

我想要一些关于在我的表中对数据子集进行编号并返回完整数据集的最佳方法的指导。

我的数据集大约是50k记录。数据库保存测试结果，每一行都是一个问题的答案。一个人做两个测试，但这些测试可能是20个左右的测试中的2个。

数据如下：

PersonID    TestID    QuestionID
UID-1       T-1       QuA-1
UID-1       T-1       QuA-2
UID-1       T-1       QaA-3
UID-1       T-2       QuB-1
UID-1       T-2       QuB-2
UID-1       T-2       QuB-3
UID-2       T-1       QuA-1
UID-2       T-3       QuC-1

我的目标是返回带有数字的数据集，以表示测试是第一次还是第二次测试(顺序并不重要，我只需要为他们的TestID分配1或2)。同样，我也需要对这些问题进行编号。

假设一个人做两个测试，第一个有15个问题，第二个有20个问题，这就是我想要的输出：

PersonID    TestID    QuestionID   TestNum   QuNum
UID-1       T-1       QuA-1        1         1
UID-1       T-1       ...          1         ...
UID-1       T-1       QaA-15       1         15
UID-1       T-2       QuB-1        2         1
UID-1       T-2       ...          2         ...
UID-1       T-2       QuB-20       2         20

对于每个人来说，测试的数量要么是1，要么是2个，测试的问题数将从1开始，并逐渐增加到最后一个问题。

你会用什么方法？

Answer 1

据我所知，在SQL中这样做的唯一方法是使用“游标”。

在声明游标之前，我从原始表中创建一个新表，并将默认值或0的两个新列(即testNum和questionNum)添加到其中。这是使用"select into“来完成的。

然后，通过光标逐个读取该表的记录(并排序)，检查personId和testId是否更改，并根据它们的状态决定(更新当前问题和测试的两个计数器)。

在循环结束时，在读取下一条记录之前，使用两个计数器更新表(用于当前问题和当前测试)。

这是代码(BTW，我还没有测试。因此，注意排印或可能出现的小错误)：

SELECT personId, testId, 0 as testNum, 0 as questionNum into t1
    FROM Questions

DECLARE c1 CURSOR FOR 
    SELECT *
        FROM t1
        ORDER BY personId, testId, questionId
OPEN c1
declare @lastPersonId varchar(10);
declare @lastTestId varchar(10);
declare @currentTestNum int;
declare @currentQuestionNum int;

set @lastPersonId = "-1";
set @lastTestId = "-1";
set @currentTestNum = 1;
set @currentQuestionNum = 1;

FETCH NEXT FROM c1
    INTO @personId, @testId, @questionId, @testNum, @qNum

WHILE @@FETCH_STATUS = 0
    BEGIN
    if (@personId <> @lastPersonId) 
        begin
        @currentTestNum = 1
        @currentQuestionNum = 1     
        end
    else
        if (@testId <> @lastTestId)         
            begin
            @currentTestNum = @currentTestNum + 1
            @currentQuestionNum = 1     
            end
        else -- in this case it is assumed that the questions are different.
            set @currentQuestionNum = @currentQuestionNum + 1

    update t1
        set testNum = @currentTestNum, questionNum = @currentQuestionNum
        where current of c1

    set @lastPersonId = @personId
    set @lastTestId = @testId
    FETCH NEXT FROM c1
        INTO @personId, @testId, @testNum, @qNum
    END  

CLOSE c1;
DEALLOCATE c1;