如何在mysql中使用聚合函数将值赋给变量? [英] How can I assign value to a variable using aggregate function in 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;

此处必须强制使用变量，因为这只是我实际任务的一个示例.
现在我想拥有超过65个标记的记录.
是否可以通过在WHERE子句中使用变量? 我没有在@var中获取实际数据，如何在WHERE子句中使用它?
您可以在此处尝试sql查询.
有什么建议吗?
谢谢

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

推荐答案

只有在预先初始化的情况下，才可以在where子句中使用用户定义的 session 变量.除非另有说明，否则由于 SQL-Query-操作顺序 ，该变量将具有默认值NULL，并且条件可能不符合预期的结果.

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 |
+-------+--------------------+---------------+------------------+

示例 @ @a SQL Fiddle > :

Example @ SQL Fiddle:

这篇关于如何在mysql中使用聚合函数将值赋给变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！