学校分数表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

相关文章：

如何在MySQL里实现行和列的互相转换？ 一些适合新手学习的SQL基础语句汇总 数据库oracle常用sql查询语句，适合新手学习练手 MySql在ONLY_FULL_GROUP_BY模式下分组查询报错问题的解决 mysql教程：慢查询问题定位及SQL优化 mysql教程：mysql解析json，mysql的json格式支持存储处理json数据 MySql教程网：mysql 拼字符串的四种方式 为什么mysql联表查询时驱动表与被驱动表的选择会极大的影响查询效率？