如果答案与带有分组依据子句的正确答案相同，则 SQL Server 添加一个分数

我有这段代码及其临时表，所以你可以运行它。

create table #student(
    id int identity(1,1),
    name varchar(50)
)
create table #quiz(
    id int identity(1,1),
    name varchar(50),
    points_worth int
)
create table #exam(
    id int identity(1,1),
    sequence int,
    question varchar(50),
    answer varchar(50),
    quiz_id int
)
create table #student_taken(
    id int identity(1,1),
    sequence int,
    answer varchar(50),
    student_id int,
    quiz_id int
)
insert into #student(name)
values('Uzumaki Naruto'),('Uchiha Sasuke'),('Haruno Sakura')
insert into #quiz(name,points_worth)
values('Chunin Exam',2)
insert into #exam(sequence,question,answer,quiz_id)
values(1,'Hinata and Neji are siblings','True',1),
(2,'Uchiha Sasuke is part of the Akatsuki','False',1),
(3,'Tsunade and Jiraiya has a son','False',1)
insert into #student_taken(sequence,answer,quiz_id,student_id)
values(1,'True',1,1),(2,'True',1,1),(3,'True',1,1),(1,'True',1,2),(2,'False',1,2),(3,'False',1,2),
(1,'True',1,3),(2,'False',1,3),(3,'False',1,3)
drop table #student
drop table #exam
drop table #quiz
drop table #student_taken

所以正如你所看到的，我漩涡鸣人只有1分，因为他只有1个正确答案，而小樱和佐助各得3分。

现在我希望它是这样的：

  id     name              score
  1      Uzumaki Naruto     2
  2      Uchiha Sasuke      6
  3      Haruno Sakura      6

他得到了 6 分，因为在我的 #quiz 表中我增加了积分价值(每个项目数量的积分价值(。

所以现在我想知道这是否需要按子句分组？正确的总和是什么，我希望如果 True = True 那么它加 1 分，False = False 相同，False = True 将不计算在内。

这是我的尝试

select 
  ST.student_id,
  SUM(1 * Q.points_worth) 'sum'
from #student_taken ST
inner join #exam E
on E.quiz_id = ST.quiz_id
inner join #quiz Q
on Q.id = E.quiz_id
group by ST.student_id

我不太确定你的问题是什么。 @JorgeCampos并不完全正确，因为仅当您在同一数据集中返回加重和非聚合字段(不使用OVER子句(时才需要GROUP BY。

至于获得结果集，我不太确定您是如何得出结论的。points_worth的值在您的测验表中，而不是考试表中，所以我假设每个问题对该测验都有相同的值？如果是这样，这是您的查询的一个想法：

SELECT q.[name] AS QuizName,
       s.[name] As StudentName,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) * q.points_worth AS Score,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) AS Correct,
       COUNT(CASE WHEN st.answer != e.answer THEN 1 END) AS Incorrect
FROM #student s
     JOIN #student_taken st ON s.id = st.student_id
     JOIN #exam e ON st.[sequence] = e.id
     JOIN #quiz q ON e.quiz_id = q.id
GROUP BY q.[name], s.[name],
         q.points_worth;

但是，我们可以更进一步，看看学生是否真的回答了所有问题(并排除那些没有回答的问题(，因此我们得到：

INSERT INTO #quiz([name],points_worth)
VALUES('Basic Maths',1);
INSERT INTO #exam([sequence],question,answer,quiz_id)
VALUES(1,'5 + 2 * 3 = 21','False',2),
      (2,'9 - 1 * 2 = 7','True',2);
INSERT INTO #student ([name])
VALUES ('Jane Smith'),('Sally Bloggs');
INSERT INTO #student_taken ([sequence],answer,quiz_id,student_id)
VALUES (1, 'false', 1, 4),
       (1, 'false', 2, 4),
       (2, 'true', 2, 4),
       (1, 'true', 2, 5);
GO
SELECT q.[name] AS QuizName,
       s.[name] As StudentName,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) * q.points_worth AS Score,
       COUNT(CASE WHEN st.answer = e.answer THEN 1 END) AS Correct,
       COUNT(CASE WHEN st.answer != e.answer THEN 1 END) AS Incorrect,
       COUNT(CASE WHEN st.answer IS NULL THEN 1 END) AS Unanswered
FROM #quiz q
     JOIN #exam e ON q.id = e.quiz_id
     CROSS JOIN #student s
     LEFT JOIN #student_taken st ON s.id = st.student_id
                                AND e.[sequence] = st.[sequence]
                                AND q.id = st.quiz_id
WHERE EXISTS (SELECT 1 FROM #student_taken sq WHERE sq.student_id = s.id AND sq.quiz_id = q.id)
GROUP BY q.[name], s.[name],
         q.points_worth;

希望有帮助。

你可以

试试这个方法：找到每个学生的正确点数(在CTE内部查询(，然后取结果并连接#quiz表，通过应用points_worth来计算最终的分数

;with cte as (
select
    st.student_id
    ,st.quiz_id
    ,COUNT(e.id) as points
from #student_taken st
left join #exam e
    on st.quiz_id = e.quiz_id
    and st.answer = e.answer
    and st.sequence = e.sequence
group by st.student_id, st.quiz_id
) select
    student_id
    ,s.name
    --,quiz_id
    ,points * q.points_worth
from cte
inner join #quiz q
    on quiz_id = q.id
inner join #student s
    on student_id = s.id

在sum中添加条件。我还注意到从#student_taken到#exam的联接无法正常工作，因为您只在quiz_id加入，而您还需要在sequence加入。

因此，这是您对这些修改的尝试：

select 
    ST.student_id,
    SUM(IIF(ST.answer = E.answer, Q.points_worth, 0)) 'sum'
from #student_taken ST
    inner join #exam E
    on E.quiz_id = ST.quiz_id and ST.sequence = E.sequence
    inner join #quiz Q
    on Q.id = st.quiz_id
group by ST.student_id

IIF函数将第一个参数计算为条件，如果为 true，则返回第二个参数，如果为 false，则返回第三个参数。因此，如果学生回答了考试的期望(ST.answer = E.answer(，则返回分数，否则为 0。

如果您不想使用 IIF ，您可以使用 case 语句：case when ST.answer = E.answer then Q.points_worth else 0 end 。我个人只是认为IIF看起来更干净，如果你搞砸了 case 语句，SQL Server Management Studio 的语法提示会有点疯狂。

相关内容

最新更新

热门标签：