mySql 分段查詢

標籤：

準備：

建立一個成績表

                Create table grade (id integer, score integer);

插入資料(只有id每次加一，score是1到100的隨機數，java產生)：

               

public class GradeInsertSentence {       public static void main(String[] args) {            for (int i = 0; i < 100; i++) {              int j = (int) (Math.random()*100) + 1;              System.out.println("insert into grade(id,score) value(‘"+i+"‘,‘"+j+"‘);");          }     }   }

 

查詢grade表的所有資料

                Select * from grade;

需求：

                查詢指定分段的人數(x>=80; 80>x>=60; 60>x>40; 40>x>=20, x<20 )

Sql：

  

實現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;

或者：

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;

 

實現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

 

這個比較尷尬的是顯示出來的結果是這樣的：

 

還有就是，如果其中一個分段的是沒有值得，那就只會顯示4條結果，最重要的是，你還不知道是哪一個分段沒有結果。。。。。

實現3：

select                 case when (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 when (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;

或

select                 case when (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 when (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;

實現4：

select A.score*20, count(A.score) from (                 select floor(g.score/20) as score from grade g )  A group by A.score;

或（有錯，不會用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;

 

 

實現5：(錯的)

               

select                 case when score BETWEEN 80 AND 100 then ‘A‘                 when score BETWEEN 60 AND 80 then ‘B‘                 when score BETWEEN 40 AND 60 then ‘C‘                 when score BETWEEN 20 AND 40 then ‘D‘                 when score < 20 then ‘E‘ end as ‘grade‘, count(*) as ‘num‘ FROM grade;

 都是在百度上找的，最後一個實現不成功，between and在select裡面不能識別範圍，哪位仁兄看到，實現了，記得給我留言，謝謝。

mySql 分段查詢