mysql實現每個專業(yè)分數段統(tǒng)計人數

student_info |?id?|name?|profession|score| |--|--|--|--| |id|姓名|分數|專業(yè)|

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;

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<p><strong>輸出sql</strong></p><pre class="brush:php;toolbar:false">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?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