如何将多行与来自两个不同表的其他行进行比较并检查它们是否相等？

格德曼南达莫洪

我有 2 个表名为OptionTextandSubmittedAns第一个表包含QuestionId OptionId和IsRight。这里IsRight用于针对问题的选项是对还是错。这里QuestionId有多个OptionId甚至可以有一个。第二个表代表用户提交的内容。他们可以根据自己的假设选择一个选项或多个选项。现在我需要制作自动脚本来证明提交的答案是对还是错。

注意：如果一个问题有多个正确选项，则用户必须选择所有正确选项，如果缺少一个，则结果将是错误的。但是，如果他选择所有正确的答案，那么答案将是正确的。

我已经尝试过这个脚本。它只能计算选定的数字，但不能证明答案是对还是错。所以我需要帮助。

我可以假设我需要一个WHILE循环来特别检查每个元素。但是如何？所以我需要帮助。这是我的代码。

CREATE TABLE OptionText(
    [OptionTextId] [bigint] IDENTITY(1,1) NOT NULL,
    [QuestionId] [bigint] NOT NULL,
    [IsRightAnswer] [bit] NOT NULL)

    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 1)    
    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 0)   
    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (5, 0)

    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 0)    
    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 1)   
    Insert into OptionText (QuestionId, IsRightAnswer) VALUES (17, 1)

CREATE TABLE SubmittedAns(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [QuestionId] [bigint] NOT NULL,
    [OptionTextId] [bigint] NOT NULL)


    Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (5, 1)    
    Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (5, 2)

    Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (2, 1)
    Insert into SubmittedAns (QuestionId, OptionTextId) VALUES (2, 1)


    select * from OptionText
    select * from SubmittedAns
    if (select count(OptionTextId) from SubmittedAns where QuestionId =5) = (select count(ot.OptionTextId) from OptionText as ot  where ot.IsRightAnswer = 1)
     select 1  as "isRight"
    else
     select 0  as "isRight"

KtX2SkD

关键材料请参阅第一行和最后一行：

SELECT CASE COUNT(*) WHEN 0 THEN 'Pass' ELSE 'Fail' END AS Boolean
FROM (
    SELECT *
    FROM #OptionText
    WHERE QuestionId = 5
      AND IsRightAnswer = 1
) AS OT
FULL OUTER JOIN #SubmittedAns AS SA ON OT.QuestionId = SA.QuestionId AND OT.OptionTextId = SA.OptionTextId
WHERE SA.QuestionId = 5
  AND OT.OptionTextId IS NULL -- This means some answers failed to be matched with your original questions/options, either because IsRightAnswer is zero, or because it doesn't exist in your questions/answers.

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。