T-SQL GroupBy Statement FAQ processing

1. Description of the problem

There is now a course table (with the course number and name) and a SC table (with student number, number of elective courses and exam results) as follows:

Now you want to check all course numbers, the corresponding course names, and the average scores of all students taking the course.
At first my idea was to use the following query statement:

select sc.cno as 课程编号, cname as 课程名称, Avg(grade) as 平均成绩from course, scwhere course.cno = sc.cnogroup by sc.cno

But obviously the error is:

And began to think of other ways.

2. The first method

The first thought of course is according to the error message, since the CNAME is not in the GROUP BY clause, then put in the GROUP BY clause, as follows:

select sc.cno as 课程编号, cname as 课程名称, Avg(grade) as 平均成绩from course, scwhere course.cno = sc.cnogroup by sc.cno, cname

But this approach is not rigorous, because this is to know that each course number only corresponds to a course name, so this approach. But in other cases, once each course number does not correspond to one course name, it is messy. So we have to think of other ways.

3. The second method

The second thought of the method is to follow the error message, the CNAME into an aggregation function on the line, as follows:

Select Sc.cnoAs course number,Max (CNAME)As course name,AVG (Grade)As average scoreFrom Course, SCwhere course.cno = Sc.cno Group by sc.cno--or select sc.cno as course code, min (CNAME) as course name, avg (grade) as average from course, Scwhere course.cno = Sc.cnogroup by sc.cno  

But this approach is less than the first method. Because the course name is a string, taking the maximum minimum value is meaningless.

4. The third method

The next step is the correct method, which is the extended SQL statement referred to in the header, which is also a nested SQL statement.
Because select...from ... , the from is followed by a table , so this table can be the result of another SELECT statement query , as follows:

select Course.cno as course number, CNAME  As course name, tmp. Average score from course, (select cno, avg (grade) < Span class= "Hljs-keyword" >as average score from sc group by cno) as tmpwhere course.cno = Tmp.cno   

In the Select...from ... A select...from is nested in the from behind and nested Select...from ... Query out the table from the individual name TMP, so that you can use TMP to manipulate the table.

5. Fourth Method

Since Select...from ... You can nest a table in the from behind, then Select...from ... Can I nest a column after a select in?
The answer is yes. As follows:

select  CNO as Course number (select cname from course where sc.cno= COURSE.CNO) as course name, AVG ( Grade) as average score from sc< span class= "kw" >group by sc.cno   

To note that in the (select cname from course where sc.cno=course.cno) must have where sc.cno=course.cno , only so the results of the query is one by one corresponding, otherwise the results of the query is many, and then each CNO will correspond to a lot of cname, so it will be confused, and this will be an error.

T-SQL GroupBy Statement FAQ processing