I have two tables to write a calculate a score table view synthesis, the structure is as follows,

Students table
Id name result_postion study_type
Zhang SAN 1 1
Zhang SAN 2 1
3 threes 3 1
4 li si 1 2
5 li si 2 2
6 li si 3 2
7 threes 1 3

Note:
The name varchar (50) name
How many times have result_postion int simulation test
Study_type int subject number

Student achievement table
Id result_postion study_type result
1 1 1 60
2 2 1 70
3 3 1 80
4 1 2 40
5, 2 2 50
6 3 2 60
7 1 3 100

Note:
How many times have result_postion int simulation
Study_type int subject number
Result int score

I'd like to merge a table, a student of the simulation test results summary calculated several times,

All hope everybody a great god grant instruction!!!!!

CodePudding user response:

Did not even with my student number, calculate the score of a hammer!

CodePudding user response:

/* student table primary key id, student number, student's name, how many times mock exam (extra), subject (extra) */

/* result table student id, how many times mock exams, discipline, scores */

With tmp1 as (
Select 1 as s_id, 'zhang' as s_name, 1 as result_postion, 1 as study_type from dual union all
Select 2 as s_id, 'zhang' as s_name, 2 as result_postion, 1 as study_type from dual union all
Select 3 as s_id, 'zhang' as s_name, 3 as result_postion, 1 as study_type from dual union all
Select 4 as s_id, 'li si as s_name, 1 as result_postion, 2 as study_type from dual union all
Select 5 as s_id, 'li si as s_name, 2 as result_postion, 2 as study_type from dual union all
Select 6 as s_id, 'li si as s_name, 3 as result_postion, 2 as study_type from dual union all
Select 7 as s_id, 'zhang' as s_name, 1 as result_postion, 3 as study_type from dual),
Tmp2 as (
Select 1 as p_id, 1 as result_postion, 1 as study_type, 60 as result from dual union all
Select 2 as p_id, 2 as result_postion, 1 as study_type, 70 as the result from dual union all
Select 3 as p_id, 3 as result_postion, 1 as study_type, 80 as the result from dual union all
Select 4 as p_id, 1 as result_postion, 2 as study_type, 40 as result from dual union all
Select 5 as p_id, 2 as result_postion, 2 as study_type, 50 as the result from dual union all
Select 6 as p_id, 3 as result_postion, 2 as study_type, 60 as result from dual union all
The select 7 as p_id, 1 as result_postion, 3 as study_type, 100 as the result from dual)
Select tmp1. Primary key id, tmp2 result_postion, sum (tmp2. Result) from tmp1 inner join tmp2 on tmp1. The primary key id=tmp2. Students table id group by tmp1. The primary key id, tmp2. Result_postion

Because do not understand your needs and you described the content and the sample data of entirely without thinking, unable to answer, can give a reference example,

CodePudding user response:

The select a.n ame, a.r esult_postion, sum (b.r esult) as the from every examination grade student table a
The join table b on student performance a.r esult_postion=b.r esult_postion and a.s tudy_type=b.s tudy_type
Group by a.n ame, a.r esult_postion


The select a.n ame, sum (b.r esult) from the as grade students watch a
The join table b on student performance a.r esult_postion=b.r esult_postion and a.s tudy_type=b.s tudy_type
Group by a.n ame

CodePudding user response:

A list - synthetic
The result for students select * from table a, b where a.r esult_postion=b.r esult_postion and a.s tudy_type=b.s tudy_type

- zhang students all mock exam grade
Select sum (b.r esult) from students table, a result table b where a.r esult_postion=b.r esult_postion and a.s tudy_type=b.s tudy_type where a.n ame='zhang'

Student performance simulation test for the first time, zhang SAN
Select sum (b.r esult) from students table, a result table b where a.r esult_postion=b.r esult_postion and a.s tudy_type=b.s tudy_type where a.n ame='zhang' and a.r esult_postion=1