Home Database Mysql Tutorial Mysql implements statistics on the number of people in each professional score segment

Mysql implements statistics on the number of people in each professional score segment

Mar 18, 2020 am 08:54 AM
mysql

Mysql implements statistics on the number of people in each professional score segment

My table structure

student_info
| id |name |profession|score|
|--|--|--|--|
|id|姓名|分数|专业|
Copy after login

Statistics by segment

400 to 500 people, Number of people between 300 and 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;
Copy after login

Mysql implements statistics on the number of people in each professional score segment

Statistics by grade and major

Number of people between 400 and 500, Number of people between 300 and 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;
Copy after login

Mysql implements statistics on the number of people in each professional score segment

sql dynamic splicing generation

  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());
Copy after login

output sql

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
Copy after login

recommended mysql video tutorial, Address: https://www.php.cn/course/list/51.html

The above is the detailed content of Mysql implements statistics on the number of people in each professional score segment. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the &quot;MySQL Native Password&quot; plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.

See all articles