mysql实现每个专业分数段统计人数

我的表结构student_info| id |name |profession|score||--|--|--|--||id|姓名|分数|专业|按分数段统计

400到500人数，300到400人数selectcount(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到400from student_info;

按分数段和专业统计

400到500人数，300到400人数selectcount(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到400from student_info GROUP BY profession;

sql动态拼接生成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());输出sqlselectcount(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 when admission_score between 530 and 539 then 1 end) as 530到539,count(case when admission_score between 540 and 549 then 1 end) as 540到549,count(case when admission_score between 550 and 559 then 1 end) as 550到559,count(case when admission_score between 560 and 569 then 1 end) as 560到569,count(case when admission_score between 570 and 579 then 1 end) as 570到579,count(case when admission_score between 580 and 589 then 1 end) as 580到589,count(case when admission_score between 590 and 599 then 1 end) as 590到599,count(case when admission_score between 600 and 609 then 1 end) as 600到609,count(case when admission_score between 610 and 619 then 1 end) as 610到619,count(case when admission_score between 620 and 629 then 1 end) as 620到629,count(case when admission_score between 630 and 639 then 1 end) as 630到639,count(case when admission_score between 640 and 649 then 1 end) as 640到649,count(case when admission_score between 650 and 659 then 1 end) as 650到659,count(case when admission_score between 660 and 669 then 1 end) as 660到669,count(case when admission_score between 670 and 679 then 1 end) as 670到679,count(case when admission_score between 680 and 689 then 1 end) as 680到689,count(case when admission_score between 690 and 699 then 1 end) as 690到699 from z_student_info推荐mysql视频教程，地址：https://www.php.cn/course/list/51.html以上就是mysql实现每个专业分数段统计人数的详细内容，更多请关注小潘博客其它相关文章！