A classic example of a comprehensive application of database segmentation and grouping queries

Remember the previous interview encountered, feel the problem is more classic, so later finishing the next.

The title description is like this (by impression):

Please use a SQL to calculate the number of people in each class, 20-50, 50-70, 70-100,

Known: Table (Exam), with the following fields: Class (Class), name (name), score (score);

Query display format:

This is a typical segmentation and grouping of query cases, the difficulty is to take into account the Group by class and according to the score sub-query.

The table data is as follows:

SelectCount_abc.class class,sum( Case  whenCount_abc.a is NULL  Then 0 Else 1 End)'20-50 of the population',     sum( Case  whenCount_abc.b is NULL  Then 0 Else 1 End)'50-70 of the population',     sum( Case  whenCount_abc.c is NULL  Then 0 Else 1 End)'70-100 of the population' from (    Select          Case  whenScorebetween  -  and  -          Then 1        EndA, Case  whenScorebetween  -  and  -          Then 1        EndB, Case  whenScorebetween  -  and  -          Then 1        EndC, Class fromexam) Count_abcGroup  byCount_abc.class;

Grouping is needless to say group by, segments are often between ... and ..., the key is how to concatenate. Because each piece of data has one feature: up to one score segment. It is easy to think of the case-when statement to express this mutex relationship.

So I think of the distribution of all the fractional sections, and a,b,c to distinguish, and then according to the different marks to accumulate summation.

The above SQL can also be simplified:

SelectCount_abc.class class,sum(COUNT_ABC.A)'20-50 of the population',     sum(COUNT_ABC.B)'50-70 of the population',     sum(COUNT_ABC.C)'70-100 of the population' from (    Select          Case  whenScorebetween  -  and  -          Then 1        Else 0        EndA, Case  whenScorebetween  -  and  -          Then 1        Else 0        EndB, Case  whenScorebetween  -  and  -          Then 1        Else 0        EndC, Class fromexam) Count_abcGroup  byCount_abc.class;

A classic example of a comprehensive application of database segmentation and grouping queries