一个经典的数据库分段查询和分组查询综合运用的例子

count_abc.class 班级, sum(case when count_abc.a is null then 0 else 1 end) ‘20-50的人数‘, sum(case when count_abc.b is null then 0 else 1 end) ‘50-70的人数‘, sum(case when count_abc.c is null then 0 else 1 end) ‘70-100的人数‘ from ( select case when score between 20 and 50 then 1 end a, case when score between 50 and 70 then 1 end b, case when score between 70 and 100 then 1 end c, class from exam ) count_abc group by count_abc.class;

分组不用说是group by，分段常用是between ... and ...，关键是如何串联。因为每一条数据都有这样一个特性：最多在一个分数段里。容易想到用case when 语句来表达这种互斥关系。

于是想到把所有的分数段分布情况，并用a,b,c区分，再根据标记不同进行累计求和。

上面的sql还可以简化：

select
    count_abc.class 班级, 
    sum(count_abc.a) ‘20-50的人数‘, 
    sum(count_abc.b) ‘50-70的人数‘, 
    sum(count_abc.c) ‘70-100的人数‘
from 

(
    select 
        case when score between 20 and 50 
        then 1
        else 0
        end a,
        case when score between 50 and 70 
        then 1
        else 0
        end b,
        case when score between 70 and 100 
        then 1
        else 0
        end c,
        class
    from exam
) count_abc
group by count_abc.class;

 

一个经典的数据库分段查询和分组查询综合运用的例子

标签：