如果答案与group by子句的正确答案相同,则SQL Server会添加分数

我有这个代码及其临时表,所以你可以运行它.

create table #student(
    id int identity(1,1),name varchar(50)
)

create table #quiz(
    id int identity(1,name varchar(50),points_worth int
)

create table #exam(
    id int identity(1,sequence int,question varchar(50),answer varchar(50),quiz_id int
)

create table #student_taken(
    id int identity(1,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',(2,'Uchiha Sasuke is part of the Akatsuki','False',(3,'Tsunade and Jiraiya has a son',1)

insert into #student_taken(sequence,quiz_id,student_id)
values(1,1,(1,2),3),3)

drop table #student
drop table #exam
drop table #quiz
drop table #student_taken

所以你可以看到我Uzumaki火影忍者只有1分,因为他只有1个正确的答案,樱花和佐助都各得3分.

现在我希望它是这样的：

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

他得到了6,因为在我的#quiz表中我增加了一些值(这是每个项目的价值).

所以现在我想知道是否需要group by子句呢？什么是正确的求和,我希望如果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不太正确,因为只有在同一数据集中返回加重和非聚合字段时才需要GROUP BY(不使用OVER子句).

至于获得结果集,我不太确定你是如何得出结论的. 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],quiz_id)
VALUES(1,'5 + 2 * 3 = 21','9 - 1 * 2 = 7',2);

INSERT INTO #student ([name])
VALUES ('Jane Smith'),('Sally Bloggs');

INSERT INTO #student_taken ([sequence],student_id)
VALUES (1,'false',4),2,'true',5);
GO

SELECT q.[name] AS QuizName,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],q.points_worth;

希望有所帮助.

如果答案与group by子句的正确答案相同,则SQL Server会添加分数

解决方法

猜你在找的MsSQL相关文章