MYSQL segmented Query

Get ready:

Create a score table

Create table grade (ID integer, score integer);

Insert data (only the ID is added one at a time, score is 1 to 100 random number, Java generation):

Public class gradeinsertsentence { Public Static void Main (string[] args) { for (int i = 0; i <; i++) { int j = (int) (Math. Random() *100) + 1; System. out. println ("INSERT into grade (Id,score) value ('" +i+ "', '" +j+ "');"); } } }

Querying all data for a grade table

Select * from grade;

Demand:

Query the number of people in the specified segment (x>=80; 80>x>=60; 60>x>40; 40>x>=20, x<20)

Sql:

Implementation 1:

SELECT * From (SELECT COUNT (*) as a from grade G where G.score >=80) A, (SELECT COUNT (*) as B from grade G where G.score >=60 and G.score <80) b, (SELECT COUNT (*) as C from grade G where G.score >=40 and G.score <60) C, (SELECT COUNT (*) as D from grade G where G.score >=20 and G.score <40) d, (SELECT COUNT (*) as E from grade G where G.score <20) e;

Or:

Select A.aa, B.bb, c.cc, D.DD, e.ee From (SELECT COUNT (*) as AA from grade G where G.score >=80) A, (SELECT COUNT (*) as BB from grade G where G.score >=60 and G.score <80) b, (SELECT COUNT (*) as CC from grade G where G.score >=40 and G.score <60) C, (SELECT COUNT (*) as DD from grade G where G.score >=20 and G.score <40) d, (SELECT COUNT (*) as EE from grade G where G.score <20) e;

Implementation 2:

Select COUNT (*) as AA from grade G where G.score >=80 UNION ALL Select COUNT (*) as BB from grade G where G.score >=60 and G.score <80 UNION ALL Select COUNT (*) as CC from grade G where G.score >=40 and G.score <60 UNION ALL Select COUNT (*) as DD from grade G where G.score >=20 and G.score <40 UNION ALL Select COUNT (*) as EE from grade g where G.score <20

The more embarrassing thing is that the results are shown here:

There is, if one of the fragments is not worth it, it will only show 4 results, most importantly, you do not know which segment has no results ....

Implementation 3:

Select Case if (score >=80) Then ' A ' When (score >=60 and score <80) then ' B ' When (score >=40 and score <60) then ' C ' When (score >=20 and score <40) then ' D ' Else ' E ' End grade, COUNT (*) num From Grade GROUP by Case if (score >=80) Then ' A ' When (score >=60 and score <80) then ' B ' When (score >=40 and score <60) then ' C ' When (score >=20 and score <40) then ' D ' Else ' E ' end Order by 1;

Or

Select Case if (score >=80) Then ' A ' When (score >=60 and score <80) then ' B ' When (score >=40 and score <60) then ' C ' When (score >=20 and score <40) then ' D ' Else ' E ' End ' Grade ', COUNT (*) num From grade GROUP BY Case if (score >=80) Then ' A ' When (score >=60 and score <80) then ' B ' When (score >=40 and score <60) then ' C ' When (score >=20 and score <40) then ' D ' Else ' E ' end;

Implementation 4:

Select A.score*20, COUNT (A.score) from ( Select Floor (G.SCORE/20) as score from grade G ) A Group BY A.score;

or ( wrong, not with convert)

Select CONVERT (A.score*20,varchar), COUNT (A.score) from ( Select Floor (G.SCORE/20) as score from grade G ) A Group BY A.score;

Implementation 5: ( wrong )

Select Case where score between and then ' A ' When score between ' B ' When score between, and then ' C ' When score between and "D" When score < Then ' E ' end as ' grade ', COUNT (*) as ' num ' from grade;

are found in Baidu, the last implementation of the unsuccessful, between and in the select can not recognize the scope, which man saw, realized, remember to give me a message, thank you.

MYSQL segmented Query