asp.net - 如何计算复选框列表值？

Question

作为我们正在进行的调查问卷设计的一部分，下面的代码用于确定调查的问题总数、正确数量以及正确答案的百分比。

我遇到的一个问题是如何计算复选框列表值。

调查以单选按钮和复选框列表值的形式出现。

使用单选按钮选择时，仅选择 1 个选项。

但对于复选框列表，可能有超过 1 个值。

这使得计算复选框列表值变得困难。

我的想法是通过以下方式计算复选框值:

-- plus 1 for each correct answer
-- minus 1 for each wrong answer
-- total from that then divided by the number of correct answers to the question

我只是不知道如何编码。

下面的代码几乎完成了上面列出的所有操作(除了复选框列表值之外)。

提前非常感谢。

SELECT question, choice, CorrectAnswer, TotalAnswers, CorrectAnswers, 
                                (CorrectAnswers * 100) / TotalAnswers AS totalPercent, 
                                convert(varchar, (CorrectAnswers * 100) / TotalAnswers ) + '%' AS totalPercentStr
                                FROM (
                                     SELECT sq.questionid, sq.question, sc.choice, sq.CorrectAnswer, 
                                       COUNT(sq.questionId) OVER (PARTITION BY sq.SurveyId) AS TotalAnswers, 
                                       COUNT(CASE WHEN sa.choiceid IS NOT NULL AND sc.choice = sq.CorrectAnswer THEN 1 ELSE NULL END) OVER (PARTITION BY sq.SurveyId) AS CorrectAnswers
                                     FROM Survey s
                                     INNER JOIN SurveyQuestions AS sq ON s.surveyId = sq.SurveyId
                                     INNER JOIN SurveyChoices   AS sc ON sq.questionId = sc.questionId
                                     LEFT JOIN SurveyAnswers    AS sa ON sc.choiceId = sa.choiceId AND sa.username = @UserName
                                     WHERE s.surveyId = @SurveyId
                                     AND (sa.username IS NOT NULL
                                     OR
                                         (sa.username IS NULL AND sc.choice = sq.CorrectAnswer))
                                    ) AS derived
                                ORDER BY questionId;

CREATE TABLE [dbo].[Survey](
  [SurveyID] [int] IDENTITY(1,1) NOT NULL,
  [Title] [varchar](50) NULL,
  [Description] [varchar](max) NULL

CREATE TABLE [dbo].[SurveyQuestions](
  [QuestionID] [int] IDENTITY(1,1) NOT NULL,
  [SurveyID] [int] NULL,
  [Question] [varchar](255) NULL,
  [AnswerType] [char](1) NULL,
  [CorrectAnswer] [nvarchar](255) NULL,
  [QuestionOrder] [int] NULL


CREATE TABLE [dbo].[SurveyChoices](
  [ChoiceID] [int] IDENTITY(1,1) NOT NULL,
  [QuestionID] [int] NOT NULL,
  [Choice] [nvarchar](255) NOT NULL

CREATE TABLE [dbo].[SurveyAnswers](
  [AnswerID] [int] IDENTITY(1,1) NOT NULL,
  [QuestionID] [int] NOT NULL,
  [ChoiceID] [int] NULL,
  [ChoiceText] [varchar](max) NULL,
  [UserName] [varchar](50) NULL

Answer 1

我建议添加一个像这样的表格:

CREATE TABLE [dbo].[CorrectAnswers](
  [AnswerID] [int] IDENTITY(1,1) NOT NULL,
  [QuestionID] [int] NOT NULL,
  [ChoiceID] [int] NULL,
  [ChoiceText] [varchar](max) NULL
)

...并用所有正确答案预先填充它。

当用户单击“提交”时，SurveyAnswers 表将被填充。我认为这已经完成了(否则，SurveyAnswers 表的意义是什么)

然后，您可以执行类似的操作来查看结果与正确答案:

SELECT 
    sc.QuestionID, 
    sc.ChoiceID, 
    case when sa.choiceID IS NOT NULL then 1 else 0 end as userSelected, 
    case when ca.choiceID IS NOT NULL then 1 else 0 end as isCorrectAnswer 
from dbo.SurveyChoices sc
LEFT JOIN dbo.SurveyAnswers sa on sc.ChoiceID = sa.choiceID AND sc.QuestionID = sa.QuestionID
LEFT JOIN dbo.CorrectAnswers ca on sc.ChoiceID = ca.choiceID AND sc.QuestionID = ca.QuestionID

请注意，如果您的 choiceID 是唯一的，则无需在连接条件中包含 QuestionID。

此外，不确定您是否需要表中的 ChoiceText 列 - 它可能只应位于一个表中，我的猜测是该表为 SurveyChoices .