Have three tables, respectively is the student table stu, curriculum cou, elective relational tables sc
Students table fields have sno sname, birthday, cno cname field of curriculum, course selection relational tables sc field has sno cno grade
Requirements: query the course not less than 2 doors of the names of the students, course gate number, request according to the descending order of course gate number, if course gate number is the same, in accordance with the name ascending order?
Excuse me each great god, and the SQL should be how to write?

CodePudding user response:

The select a.s name, count (biggest) from stu a, cno b where a.s no=b.s no group by a.s name having count (biggest) & gt; Desc=2 order by count (biggest), sname asc

CodePudding user response:

The select s.s name, c.s. ccount
The from stu s
The join (select sno, count (sno) as sccount from sc group by sno) c on s.s c.s. no no=
C.s. ccount where & gt;=2
The order by s.s name asc.

CodePudding user response:

The select a.s name, count (1) t
The from stu a
Inner join sc b on a.s no=b.s no
Inner join cou c on the biggest no=Arthur c. no
Where t> 2
Group by a.s name
The order by t desc, a.s name asc.