mysql 按分数段,每个专业分数段统计人数

我的表结构

student_info

idnameprofessionscore
id姓名分数专业

按分数段统计

400到500人数,300到400人数

select
count(case when score between 400 and 500 then 1 end) as 400500,
count(case when score between 300 and 400 then 1 end) as 300400
from student_info;

在这里插入图片描述

按分数段和专业统计

400到500人数,300到400人数

select
count(case when score between 400 and 500 then 1 end) as 400500,
count(case when score between 300 and 400 then 1 end) as 300400
from 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());

输出sql

select  count(case when admission_score between 200 and 209 then 1 end) as 200209,  count(case when admission_score between 210 and 219 then 1 end) as 210219,  count(case when admission_score between 220 and 229 then 1 end) as 220229,  count(case when admission_score between 230 and 239 then 1 end) as 230239,  count(case when admission_score between 240 and 249 then 1 end) as 240249,  count(case when admission_score between 250 and 259 then 1 end) as 250259,  count(case when admission_score between 260 and 269 then 1 end) as 260269,  count(case when admission_score between 270 and 279 then 1 end) as 270279,  count(case when admission_score between 280 and 289 then 1 end) as 280289,  count(case when admission_score between 290 and 299 then 1 end) as 290299,  count(case when admission_score between 300 and 309 then 1 end) as 300309,  count(case when admission_score between 310 and 319 then 1 end) as 310319,  count(case when admission_score between 320 and 329 then 1 end) as 320329,  count(case when admission_score between 330 and 339 then 1 end) as 330339,  count(case when admission_score between 340 and 349 then 1 end) as 340349,  count(case when admission_score between 350 and 359 then 1 end) as 350359,  count(case when admission_score between 360 and 369 then 1 end) as 360369,  count(case when admission_score between 370 and 379 then 1 end) as 370379,  count(case when admission_score between 380 and 389 then 1 end) as 380389,  count(case when admission_score between 390 and 399 then 1 end) as 390399,  count(case when admission_score between 400 and 409 then 1 end) as 400409,  count(case when admission_score between 410 and 419 then 1 end) as 410419,  count(case when admission_score between 420 and 429 then 1 end) as 420429,  count(case when admission_score between 430 and 439 then 1 end) as 430439,  count(case when admission_score between 440 and 449 then 1 end) as 440449,  count(case when admission_score between 450 and 459 then 1 end) as 450459,  count(case when admission_score between 460 and 469 then 1 end) as 460469,  count(case when admission_score between 470 and 479 then 1 end) as 470479,  count(case when admission_score between 480 and 489 then 1 end) as 480489,  count(case when admission_score between 490 and 499 then 1 end) as 490499,  count(case when admission_score between 500 and 509 then 1 end) as 500509,  count(case when admission_score between 510 and 519 then 1 end) as 510519,  count(case when admission_score between 520 and 529 then 1 end) as 520529,  count(case when admission_score between 530 and 539 then 1 end) as 530539,  count(case when admission_score between 540 and 549 then 1 end) as 540549,  count(case when admission_score between 550 and 559 then 1 end) as 550559,  count(case when admission_score between 560 and 569 then 1 end) as 560569,  count(case when admission_score between 570 and 579 then 1 end) as 570579,  count(case when admission_score between 580 and 589 then 1 end) as 580589,  count(case when admission_score between 590 and 599 then 1 end) as 590599,  count(case when admission_score between 600 and 609 then 1 end) as 600609,  count(case when admission_score between 610 and 619 then 1 end) as 610619,  count(case when admission_score between 620 and 629 then 1 end) as 620629,  count(case when admission_score between 630 and 639 then 1 end) as 630639,  count(case when admission_score between 640 and 649 then 1 end) as 640649,  count(case when admission_score between 650 and 659 then 1 end) as 650659,  count(case when admission_score between 660 and 669 then 1 end) as 660669,  count(case when admission_score between 670 and 679 then 1 end) as 670679,  count(case when admission_score between 680 and 689 then 1 end) as 680689,  count(case when admission_score between 690 and 699 then 1 end) as 690699 from z_student_info
有理想的coder CSDN认证博客专家 全栈工程师 终生学习者 懂营销的程序猿
坐标郑州,从业经验10余年,擅长javaweb技术栈,实战经验丰富。目前感兴趣方向:打造副业,网络安全,高可用高并发,架构,营销。更多干货请关注微信公众号lovepythoncn,关注我交个朋友!
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页