将两个查询合并为一个

Question

我需要写一个查询，查找每个类名的平均值，每个学生的总平均值，我已经为每个类名编写了两个单独的查询，但是如何组合成一个查询呢？

我的第一个问题是：

select distinct  StudentFirstName,StudentLastName, ClassName, avg(Grade) as 'average for this subject'
from tests 
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName,ClassName;

我的第二个问题：

select distinct  StudentFirstName,StudentLastName, avg(Grade) as 'total average'
from tests 
inner join students on tests.StudentID=students.StudentID
group by StudentFirstName,StudentLastName;

学生桌：

  pk-INT           VARCHAR             VARCHAR
+-----------+------------------+-----------------+
| StudentID | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
|         1 | agam             | rafaeli         |
|         2 | amir             | aizinger        |
|         3 | avi              | caspi           |
|         4 | avia             | wolf            |
|         5 | ben              | moskovich       |
|         6 | chen             | segalovich      |
|         7 | dana             | levy            |
|         8 | daniel           | marcus          |
|         9 | daphna           | chwarts         |
|        10 | david            | cohen           |
+-----------+------------------+-----------------+

测试表：

  PK-VARCHR    PK-VARCHR     PK&FK-INT   INT
+------------+------------+-----------+-------+
| TestDate   | ClassName  | StudentID | Grade |
+------------+------------+-----------+-------+
| 2017-07-01 | Algebra    |         1 |    88 |
| 2017-08-02 | Algo       |         1 |    97 |
| 2017-09-01 | Algebra    |         1 |    80 |
| 2017-09-01 | Algebra    |         1 |    97 |
| 2017-09-01 | Set-theory |         1 |    85 |
| 2017-09-04 | Calcules   |         1 |    86 |
| 2016-05-03 | Set-theory |         2 |    84 |
| 2016-07-02 | Calcules   |         2 |    89 |
| 2016-07-04 | Algo       |         2 |    83 |
| 2016-07-05 | Algebra    |         2 |    79 |
| 2016-06-03 | Algebra    |         3 |    99 |
| 2016-07-02 | Algo       |         3 |    97 |
| 2016-07-03 | Calcules   |         3 |    96 |
| 2016-09-03 | Set-theory |         3 |    95 |
| 2016-06-03 | Algebra    |         4 |    78 |
+------------+------------+-----------+-------+

Answer 1

您要查找的内容可以使用WITH ROLLUP进行一次查询。

提出的查询

SELECT BB.StudentFirstName,BB.StudentLastName,
IFNULL(AA.ClassName,
CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
AA.StudentAverage FROM
(select A.StudentID,A.ClassName,avg(Grade) StudentAverage
from tests A inner join students B on A.StudentID=B.StudentID
group by A.StudentID,A.ClassName WITH ROLLUP) AA
inner join students BB on AA.StudentID=BB.StudentID;

样本数据

DROP DATABASE IF EXISTS error404;
CREATE DATABASE error404;
USE error404
CREATE TABLE students
(
    StudentID        INT NOT NULL AUTO_INCREMENT,
    StudentFirstName VARCHAR(25),
    StudentLastName  VARCHAR(25),
    PRIMARY KEY (StudentID)
);
INSERT INTO students (StudentFirstName,StudentLastName) VALUES
('agam'  , 'rafaeli'), ('amir'  , 'aizinger'), ('avi'   , 'caspi'),
('avia'  , 'wolf '), ('ben'   , 'moskovich'), ('chen'  , 'segalovich'),
('dana'  , 'levy '), ('daniel', 'marcus'), ('daphna', 'chwarts');
CREATE TABLE tests
(
    testid    INT NOT NULL AUTO_INCREMENT,
    TestDate  DATE,
    ClassName VARCHAR(25),
    StudentID INT NOT NULL,
    Grade     INT NOT NULL,
    PRIMARY KEY (testid),
    KEY (StudentID)
);
INSERT INTO tests (TestDate,ClassName,StudentID,Grade) VALUES
('2017-07-01', 'Algebra'    , 1 , 88 ), ('2017-08-02', 'Algo'       , 1 , 97 ),
('2017-09-01', 'Algebra'    , 1 , 80 ), ('2017-09-01', 'Algebra'    , 1 , 97 ),
('2017-09-01', 'Set-theory' , 1 , 85 ), ('2017-09-04', 'Calcules'   , 1 , 86 ),
('2016-05-03', 'Set-theory' , 2 , 84 ), ('2016-07-02', 'Calcules'   , 2 , 89 ),
('2016-07-04', 'Algo'       , 2 , 83 ), ('2016-07-05', 'Algebra'    , 2 , 79 ),
('2016-06-03', 'Algebra'    , 3 , 99 ), ('2016-07-02', 'Algo'       , 3 , 97 ),
('2016-07-03', 'Calcules'   , 3 , 96 ), ('2016-09-03', 'Set-theory' , 3 , 95 ),
('2016-06-03', 'Algebra'    , 4 , 78 );

建议的查询执行

mysql> SELECT BB.StudentFirstName,BB.StudentLastName,
    -> IFNULL(AA.ClassName,
    -> CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
    -> AA.StudentAverage FROM
    -> (select A.StudentID,A.ClassName,avg(Grade) StudentAverage
    -> from tests A inner join students B on A.StudentID=B.StudentID
    -> group by A.StudentID,A.ClassName WITH ROLLUP) AA
    -> inner join students BB on AA.StudentID=BB.StudentID;
+------------------+-----------------+-------------------------------+----------------+
| StudentFirstName | StudentLastName | ClassName                     | StudentAverage |
+------------------+-----------------+-------------------------------+----------------+
| agam             | rafaeli         | Algebra                       |        88.3333 |
| agam             | rafaeli         | Algo                          |        97.0000 |
| agam             | rafaeli         | Calcules                      |        86.0000 |
| agam             | rafaeli         | Set-theory                    |        85.0000 |
| agam             | rafaeli         | All Classes for agam rafaeli  |        88.8333 |
| amir             | aizinger        | Algebra                       |        79.0000 |
| amir             | aizinger        | Algo                          |        83.0000 |
| amir             | aizinger        | Calcules                      |        89.0000 |
| amir             | aizinger        | Set-theory                    |        84.0000 |
| amir             | aizinger        | All Classes for amir aizinger |        83.7500 |
| avi              | caspi           | Algebra                       |        99.0000 |
| avi              | caspi           | Algo                          |        97.0000 |
| avi              | caspi           | Calcules                      |        96.0000 |
| avi              | caspi           | Set-theory                    |        95.0000 |
| avi              | caspi           | All Classes for avi caspi     |        96.7500 |
| avia             | wolf            | Algebra                       |        78.0000 |
| avia             | wolf            | All Classes for avia wolf     |        78.0000 |
+------------------+-----------------+-------------------------------+----------------+
17 rows in set (0.00 sec)

奖金查询

这个查询包括所有的学生，不管他们是否上过课。

SELECT BB.StudentFirstName,BB.StudentLastName,
IFNULL(AA.ClassName,
CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
IFNULL(AA.StudentAverage,0) StudentAverage FROM
students BB LEFT JOIN
(select A.StudentID,A.ClassName,avg(Grade) StudentAverage
from tests A inner join students B on A.StudentID=B.StudentID
group by A.StudentID,A.ClassName WITH ROLLUP) AA
on AA.StudentID=BB.StudentID;

执行

加值查询

mysql> SELECT BB.StudentFirstName,BB.StudentLastName,
    -> IFNULL(AA.ClassName,
    -> CONCAT('All Classes for ',BB.StudentFirstName,' ',BB.StudentLastName)) ClassName,
    -> IFNULL(AA.StudentAverage,0) StudentAverage FROM
    -> students BB LEFT JOIN
    -> (select A.StudentID,A.ClassName,avg(Grade) StudentAverage
    -> from tests A inner join students B on A.StudentID=B.StudentID
    -> group by A.StudentID,A.ClassName WITH ROLLUP) AA
    -> on AA.StudentID=BB.StudentID;
+------------------+-----------------+---------------------------------+----------------+
| StudentFirstName | StudentLastName | ClassName                       | StudentAverage |
+------------------+-----------------+---------------------------------+----------------+
| agam             | rafaeli         | Algebra                         |        88.3333 |
| agam             | rafaeli         | Algo                            |        97.0000 |
| agam             | rafaeli         | Calcules                        |        86.0000 |
| agam             | rafaeli         | Set-theory                      |        85.0000 |
| agam             | rafaeli         | All Classes for agam rafaeli    |        88.8333 |
| amir             | aizinger        | Algebra                         |        79.0000 |
| amir             | aizinger        | Algo                            |        83.0000 |
| amir             | aizinger        | Calcules                        |        89.0000 |
| amir             | aizinger        | Set-theory                      |        84.0000 |
| amir             | aizinger        | All Classes for amir aizinger   |        83.7500 |
| avi              | caspi           | Algebra                         |        99.0000 |
| avi              | caspi           | Algo                            |        97.0000 |
| avi              | caspi           | Calcules                        |        96.0000 |
| avi              | caspi           | Set-theory                      |        95.0000 |
| avi              | caspi           | All Classes for avi caspi       |        96.7500 |
| avia             | wolf            | All Classes for avia wolf       |        78.0000 |
| avia             | wolf            | Algebra                         |        78.0000 |
| ben              | moskovich       | All Classes for ben moskovich   |         0.0000 |
| chen             | segalovich      | All Classes for chen segalovich |         0.0000 |
| dana             | levy            | All Classes for dana levy       |         0.0000 |
| daniel           | marcus          | All Classes for daniel marcus   |         0.0000 |
| daphna           | chwarts         | All Classes for daphna chwarts  |         0.0000 |
+------------------+-----------------+---------------------------------+----------------+
22 rows in set (0.00 sec)

试试看！

Answer 2

这是不可能的，因为这两个查询有不同的粒度。你必须用像sum(grades) / count(distinct full-student-name)这样的公式自己计算一个平均值。(顺便说一句，您没有必要使用distinct，group by已经为您做到了)

这样你会得到正确的计数，但你会加太多的分数，因为它们被乘以分组为类。你不能做sum(distinct grades)，因为一个学生可以在不同的班级有相同的成绩。

你也不能计算平均值的平均值。这通常是接近，但仍然不正确。

Answer 3

使用相关子查询尝试此解决方案。

SELECT   StudentFirstName,
         StudentLastName,
         ClassName,
         (SELECT Avg(Grade) FROM Tests WHERE ClassName = t.ClassName AND StudentId =s.StudentId) AS StudentClassAverage,
         (SELECT Avg(Grade) FROM Tests WHERE StudentId =s.StudentId) AS StudentAverage
FROM     Students s
         JOIN Tests t
             ON s.StudentId = t.StudentId
GROUP BY s.StudentId

更新2017-12-05 11:20来自RolandoMySQLDBA

根据Verace的评论，我确实将sql_mode设置为空白。这是输出

mysql> SELECT   StudentFirstName,
    ->          StudentLastName,
    ->          ClassName,
    ->          (SELECT Avg(Grade) FROM Tests WHERE ClassName = t.ClassName AND StudentId =s.StudentId) AS StudentClassAverage,
    ->          (SELECT Avg(Grade) FROM Tests WHERE StudentId =s.StudentId) AS StudentAverage
    -> FROM     Students s
    ->          JOIN Tests t
    ->              ON s.StudentId = t.StudentId
    -> GROUP BY s.StudentId;
+------------------+-----------------+------------+---------------------+----------------+
| StudentFirstName | StudentLastName | ClassName  | StudentClassAverage | StudentAverage |
+------------------+-----------------+------------+---------------------+----------------+
| agam             | rafaeli         | Algebra    |             88.3333 |        88.8333 |
| amir             | aizinger        | Set-theory |             84.0000 |        83.7500 |
| avi              | caspi           | Algebra    |             99.0000 |        96.7500 |
| avia             | wolf            | Algebra    |             78.0000 |        78.0000 |
+------------------+-----------------+------------+---------------------+----------------+
4 rows in set (0.00 sec)

mysql>

虽然并不是所有的科目都出现在每一个参加测试的学生身上，但是平均数据对于检索到的内容是正确的。这里的挑战将是调整对每个主题的平均值的查询。我将对此进行讨论，因为相关的子查询有时确实会令人头疼，但也不可避免地会完成这项工作。