SQL programming: Count the scores of n tests

Recently, I helped the school to set up a score entry system, which has the following task requirements:

Requirement Description:

There are three tables:

1. You can find all students corresponding to the course in the web_inputCj_cjb table based on the course id.

2. Based on the course id, you can find the n tests corresponding to the course from web_inputCj_xsksb. The n tests contain the ksid (Test id), score ratio, and other information.

3. Based on the Test id, you can find the information of all students in each test from web_inputCj_xscjb (xh (student id), cj (score of this test ))

Task:

Based on this information, according to the ratio of the scores of each exam (0 ~ 100) calculate the total score of the student and update it to the cj field of web_inputCj_cj.

Solution:

1. First, you need to calculate the overall score of the students, which is also the most critical

?

1 2 3 4 5 6 7 8 9

Select zzcj. cj, zzcj. xh from (select xh from web_inputCj_cjb where kcId = @ kcid) xsxx left join (select xh, round (sum (cjbl * 0.01 * isnull (cj, 0), 0) cj from web_inputCj_xscjb xscjb left join (select ksid, cjbl from web_inputCj_xsksb where kcId = @ kcid) ksxx on xscjb. ksid = ksxx. ksid group by xh) zzcj on zzcj. xh = xsxx. xh

2. With this "Exam lead table", all the data in the table is updated to the target table. Here I use a cursor to implement it. The complete SQL statement is as follows:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 37

USE [new_wzjw] GO/****** Object: StoredProcedure [dbo]. [Score Management _ process Score Input _ final score Statistics] Script Date: 19:22:01 ******/SET ANSI_NULLS on go set QUOTED_IDENTIFIER on go alter proc [dbo]. [Score Management _ process Score Input _ final score Statistics] @ kcid jxrw_code as begin declare c1 cursor for select zzcj. cj, zzcj. xh from (select xh from web_inputCj_cjb where kcId = @ kcid) xsxx left join (select xh, round (sum (cjbl * 0.01 * isnull (cj, 0), 0) cj from web_inputCj_xscjb xscjb left join (select ksid, cjbl from web_inputCj_xsksb where kcId = @ kcid) ksxx on xscjb. ksid = ksxx. ksid group by xh) zzcj on zzcj. xh = xsxx. xh declare @ cj float declare @ xh varchar (12) open c1 fetch c1 into @ cj, @ xh while @ FETCH_STATUS = 0 begin update web_inputCj_cjb set cj = @ cj where xh = @ xh and kcId = @ kcId fetch c1 into @ cj, @ xh end