学校分数表infoTable

 id | school  | grade | score
    1 | school A| A     | 100
    2 | school B| D     | 25
    3 | school B| C     | 50
    4 | school A| B     | 67
    5 | school C| B     | 70
    6 | school A| D     | 10
    6 | school B| A     | 85

首先想查出学校A和其它学校的平均分,分开来可以这样写

SELECT school,AVG(score) as average FROM infoTable where school = 'school A';
SELECT school,AVG(score) as average FROM infoTable where school != 'school A';

但如果想用一条sql直接查出来下面这样的结果

school   | school_a_average | other_school_average
school A |  60              | 70

可以怎么写呢?看如下sql,可以使用聚合函数

SELECT
    'school A'
    AVG(CASE WHEN school = 'school A' THEN score END) AS school_a_average,
    AVG(CASE WHEN school != 'school A' THEN score END) AS other_school_average,
    SUM(school = 'school A') - SUM(school != 'school A') AS diff
FROM infoTable;

本文由《MySql教程网》原创,转载请注明出处!https://mysql360.com