insert和if语句来计算成绩 [英] insert and if statements to calculate grades

问题描述

HI，



基本上我正在创建一个学生测试软件，我的表结构的一部分如下：



RESULTS_TBL：QPaper_ID，Marks，Class，Subject，Grade，Student_ID [此表存储基于其性能的计算结果]



ANSWERS_TBL ：QPaper_ID，Marks，Class，Subject，Student_ID [这个表仅存储学生提供的正确答案和每个问题的标记]



我想计算结果主要是Grade并将ANSWERS_TBL中的值插入到RESULTS_TBL中。以下是我的代码的一部分：

Basically I'm creating a student test-software and a part of my table structure is as follows:

RESULTS_TBL: QPaper_ID, Marks, Class, Subject, Grade, Student_ID [THIS TABLE STORES THE CALCULATED RESULTS BASED ON HIS PERFORMANCE]

ANSWERS_TBL: QPaper_ID, Marks, Class, Subject, Student_ID [THIS TABLE STORES ONLY THE CORRECT ANSWERS GIVEN BY THE STUDENTS AND THE MARKS FOR EACH QUESTION ]

I want to calculate the results mainly the Grade and insert the values from ANSWERS_TBL into the RESULTS_TBL . Below is a part of my code:

DECLARE @A_G INT, @B_G INT, @C_G INT, @D_G INT
SET @A_G = 40
SET @B_G = 30
SET @C_G = 10
INSERT INTO RESULTS ( QPaper_ID, Marks,Student_ID, Class, Grade)
select QPaper_ID, sum(Marks), Student_ID, Class,
case
  when  sum(Marks) > @A_G then 'A'
  when sum(Marks) between @B_G and @A_G then 'B'
  when sum(Marks) between @C_G and @B_G then 'C'
  else  'D'
END
 from ANSWERS where QP_ID = 1000 and Login_ID = 'a'
 GROUP BY QPaper_ID ,Marks, Student_ID, Class 

当我运行此查询时，ANSWERS中的所有行都被插入到我不想要的结果中。我只想要一个记录来计算每个问题的标记总和以及获得的等级。 BTW是正确的代码。



谢谢

When I run this query, all the rows from ANSWERS are inserted into RESULTS which I don't want. I want only one record which calculates the sum of marks for each question along with the grade obtained. BTW is the code correct.

THANKS

推荐答案

你应该打破选择并让它工作你想要的方式，然后把它放回一个插入语句下面。





如果你想要每个学生的分数，你应该按学生分组首先，我想。如果要对标记求和，则不应按标记分组。 GRoup by指定用于创建要汇总的一组标记的组。按标记绘制，为每组标记提供一组，因此sum（标记）等于该行的标记。

You should break out the select and make it work the way you want, then put it back below an insert statement.


If you want marks per student, you should group by studentid first, I think. If you want to sum marks, you should not group by marks. GRoup by specifies the group that is used to create a group of marks to sum. GRouping by marks, gives you one group for each set of marks, so sum(marks) equals marks for that row.

DECLARE @A_G INT, @B_G INT, @C_G INT, @D_G INT
SET @A_G = 40
SET @B_G = 30
SET @C_G = 10
INSERT INTO RESULTS ( QPaper_ID, Marks,Student_ID, Class, Grade)
select QPaper_ID, sum(Marks), Student_ID, Class,
case
  when  sum(Marks) > @A_G then 'A'
  when sum(Marks) between @B_G and @A_G then 'B'
  when sum(Marks) between @C_G and @B_G then 'C'
  else  'D'
END
 from ANSWERS where QP_ID = 1000 and Login_ID = 'a'
 GROUP BY QPaper_ID , Student_ID, Class

这篇关于insert和if语句来计算成绩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！