当前位置 博文首页 > 程序员石磊：mysql 按分数段，每个专业分数段统计人数
student_info
| id | name | profession | score |
|---|---|---|---|
| id | 姓名 | 分数 | 专业 |
400到500人数，300到400人数
select
count(case when score between 400 and 500 then 1 end) as 400到500,
count(case when score between 300 and 400 then 1 end) as 300到400
from student_info;
400到500人数，300到400人数
select
count(case when score between 400 and 500 then 1 end) as 400到500,
count(case when score between 300 and 400 then 1 end) as 300到400
from student_info GROUP BY profession;
int start = 200;
int end = 700;
int inter = 10;
int count = (end-start)/inter;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.append("select ");
for(int i =1;i<=count;i++){
int next = start+inter-1;
System.out.println(start + " \t" + next);
sqlBuilder.append(" count(case when admission_score between ").append(start).append(" and ").append(next).append(" then 1 end) as ").append(start).append("到").append(next);
if(i!=count){
sqlBuilder.append(", ");
}
start += inter;
}
sqlBuilder.append(" from z_student_info");
System.out.println(sqlBuilder.toString());
select count(case when admission_score between 200 and 209 then 1 end) as 200到209, count(case when admission_score between 210 and 219 then 1 end) as 210到219, count(case when admission_score between 220 and 229 then 1 end) as 220到229, count(case when admission_score between 230 and 239 then 1 end) as 230到239, count(case when admission_score between 240 and 249 then 1 end) as 240到249, count(case when admission_score between 250 and 259 then 1 end) as 250到259, count(case when admission_score between 260 and 269 then 1 end) as 260到269, count(case when admission_score between 270 and 279 then 1 end) as 270到279, count(case when admission_score between 280 and 289 then 1 end) as 280到289, count(case when admission_score between 290 and 299 then 1 end) as 290到299, count(case when admission_score between 300 and 309 then 1 end) as 300到309, count(case when admission_score between 310 and 319 then 1 end) as 310到319, count(case when admission_score between 320 and 329 then 1 end) as 320到329, count(case when admission_score between 330 and 339 then 1 end) as 330到339, count(case when admission_score between 340 and 349 then 1 end) as 340到349, count(case when admission_score between 350 and 359 then 1 end) as 350到359, count(case when admission_score between 360 and 369 then 1 end) as 360到369, count(case when admission_score between 370 and 379 then 1 end) as 370到379, count(case when admission_score between 380 and 389 then 1 end) as 380到389, count(case when admission_score between 390 and 399 then 1 end) as 390到399, count(case when admission_score between 400 and 409 then 1 end) as 400到409, count(case when admission_score between 410 and 419 then 1 end) as 410到419, count(case when admission_score between 420 and 429 then 1 end) as 420到429, count(case when admission_score between 430 and 439 then 1 end) as 430到439, count(case when admission_score between 440 and 449 then 1 end) as 440到449, count(case when admission_score between 450 and 459 then 1 end) as 450到459, count(case when admission_score between 460 and 469 then 1 end) as 460到469, count(case when admission_score between 470 and 479 then 1 end) as 470到479, count(case when admission_score between 480 and 489 then 1 end) as 480到489, count(case when admission_score between 490 and 499 then 1 end) as 490到499, count(case when admission_score between 500 and 509 then 1 end) as 500到509, count(case when admission_score between 510 and 519 then 1 end) as 510到519, count(case when admission_score between 520 and 529 then 1 end) as 520到529, count(case
