SQL语句面试题

Question

问题描述一张表，id class score，一条SQL语句，获得每个班级大于60分和小于60分的个数，结果为:班级 大于60 小于60A1 2 3A2 1 2补充采纳了答案,我在想有没有更简单或者效率更高的sql语句

Answer 1

select class count(score as sc_under60) count(score as sc_above60)

from 表

where sc_under60 < 60 and sc_above60 > 60

group by class;

是这个意思么？

Answer 2

是这样吧。

SELECT

  class as 班级,

  (select count(score) from score c1 where score > 60 and c.class = c1.class) as 大于60,

  (select count(score) from score c2 where score < 60 and c.class = c2.class) as 小于60

FROM

    score c

GROUP BY

    class;