To find the student number of the student with the highest average score, how to write a SQL query to associate the grades with the student table?-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

To find the student number of the student with the highest average score, how to write a SQL query to associate the grades with the student table?

P粉786432579 2023-08-15 15:11:01

473

这是我的学生表 stud 表

这是我的成绩表 marks 表

rollno	mar	eng	maths	phy	chem
1	40	45	38	50	50
2	28	50	45	41	38
3	41	42	43	44	45
4	45	44	43	42	41
5	33	32	42	15	41

对于以下查询：

select rollno,(mar+eng+maths+phy+chem)/5 as average from marks;

我得到的结果是：op

现在要找到平均分最高的学生的学号，我应该写什么查询？

我尝试使用

select rollno,max( select (mar+eng+maths+phy+chem)/5 from marks ) from marks;

但是给我返回了语法错误

P粉786432579

reply all (1)

P粉0662240862023-08-16 09:52:46 1 floor

This syntax is wrong because the subquery returns multiple rows. Only one value can be used in the max function.

This is a better way -

SELECT rollno FROM marks WHERE (mar+eng+maths+phy+chem)/5 = (SELECT MAX((mar+eng+maths+phy+chem)/5) FROM marks);

Like+0

Add Reply

Php8, I'm coming too