mysql运行函数参数赋值,如何在mysql中使用聚合函数为变量赋值？

This is my table structure and data.

create table StudentInformation

(

sId INT(5),

name VARCHAR(50),

sClass VARCHAR(10),

maths INT(5),

physics INT(5),

chemistry INT(5)

);

INSERT INTO StudentInformation

values

(1, 'Jai', '11th', 60, 75, 65),

(2, 'Leela', '12th', 91, 87, 94),

(3, 'Suresh', '11th', 75, 68, 70),

(4, 'Ramesh', '11th', 50, 67, 55),

(5, 'Janki', '12th', 78, 89, 78),

(6, 'Lalu', '12th', 30, 38, 45),

(7, 'Amit', '11th', 91, 95, 93),

(8, 'Komal', '11th', 66, 78, 74),

(9, 'Sanjay', '12th', 25, 40, 35);

Now I want to calculate average marks for each class.

I have tried this query :

SELECT

sClass class,

@var := sum(maths+physics+chemistry)/(count(sid)*3) as avgMarksPerSubject,

@var as variableValue,

count(sid) as numberOfStudents

FROM StudentInformation

#where @var > 65

group by sClass;

Use of variable is compulsory here as this is just an example of my actual task.

Now I would like to have those records which have more than 65 marks.

Is it possible by using variable in WHERE clause ?

I am not getting actual data in @var, how can I use it in WHERE clause ?

You can try sql query here.

Any suggestion ?

Thanks

解决方案

Using a user defined session variable in where clause is only possible when it is pre initialized. Unless otherwise, due to the SQL-Query-Order-of-Operations, the variable will be having a default NULL and the condition may not satisfy the results as expected.

set @var:=0;

SELECT

sClass class,

@var := cast(sum(maths+physics+chemistry)

/(count(sid)*3) as decimal(6,2)

) as avgMarksPerSubject,

@var as variableValue,

count(sid) as numberOfStudents

FROM StudentInformation

where @var < 65

group by sClass

;

+-------+--------------------+---------------+------------------+

| CLASS | AVGMARKSPERSUBJECT | VARIABLEVALUE | NUMBEROFSTUDENTS |

+-------+--------------------+---------------+------------------+

| 11th | 72.13 | 0 | 5 |

| 12th | 60.83 | 0 | 4 |

+-------+--------------------+---------------+------------------+

Here you can clearly see that the variable is not assigned any value per row and from the value calculated in the previous column expression.

You can see its side effects by running the following query:

select * from (

SELECT

sClass class,

@var := cast(sum(maths+physics+chemistry)

/(count(sid)*3) as decimal(6,2)

) as avgMarksPerSubject,

@var as variableValue,

count(sid) as numberOfStudents

FROM StudentInformation

group by sClass

) r where avgMarksPerSubject > 65

+-------+--------------------+---------------+------------------+

| CLASS | AVGMARKSPERSUBJECT | VARIABLEVALUE | NUMBEROFSTUDENTS |

+-------+--------------------+---------------+------------------+

| 11th | 72.13 | 60.83 | 5 |

+-------+--------------------+---------------+------------------+