Database for nested query join query-CodePudding

Courses more than his average score of each student to find courses, can need not nested query, use other method?
(what other nested query can be converted to join queries?)

CodePudding user response:

refer to the original poster m0_46720190 response:

courses more than his average score of each student to find courses, can need not nested query, use other method?
(what other nested query can be converted to join queries?)

Give a table structure and the sample data, query and how to write table structure has a direct relationship,

CodePudding user response:

reference 1st floor _Asura response:

Quote: refer to the original poster m0_46720190 response:

Courses more than his average score of each student to find courses, can need not nested query, use other method?
(what other nested query can be converted to join queries?)

Give a table structure and the sample data, the query how to write and table structure has a direct relationship,

Don't reply hair figure I direct messages to you

CodePudding user response:

Make a reply here, also convenient great god help see if there are other better way, the original poster is given table structure is as follows:

 
If object_id (' tempdb for.. # # ') sc is not null drop table sc 
The create table # sc 
(Sno nvarchar (50), Cno nvarchar (50), Grade int) 
Insert into # sc 
Students select '1', 'class 1', '92' union all 
Select 'students' 1', 'course 2', '85' union all 
Select 'students' 1', '3' course, '88' union all 
Students select '2', 'class 1', '90' union all 
Students select '2', 'course 2', '80' union all 
Students select '2', '3' course, '59' 

Select a. *, t. averages from # sc a 
Left the join (
Select Sno, avg (Grade) as average score 
The from # sc 
Group by Sno 
T on a.S no=t.S) no 
Where al-qeada rade>=t. average score

According to the requirements of the building Lord, I understand the meaning is: students take 3 1 course (course 1, 2, 3), 3 course total score is 265, an average of about 88, then find out the student 1 score greater than or equal to 88 courses of all courses, so that the students meet 1 have course 1 and 3,

The other needs not use nested query table joins instead, averaging can use aggregate functions Avg (), once use Avg aggregation function, Sum, Max, Min, the Count will not gathered in the query field, even if use the Stuff string function, also is equivalent to a nested query,

CodePudding user response:

If it's not a nested query is a software level limit, can put the nested statements written in view, so that it can be directly on the application layer select * from view ~ ~ ~