Detailed explanation of how MySQL calculates the median of data

The following content mainly gives you a detailed explanation of how MySQL calculates the median data. The knowledge mentioned here, which is slightly different from books, is summarized by professional and technical personnel in the process of contact with users, and has a certain value of experience sharing. I hope to bring help to the majority of readers.

To get the median of a set of data (such as the median revenue of a region or company), we typically subdivide this task into three small tasks:

1. Sort the data and give each row its ranking among all the data

two。 Find out the ranking number of the median

3. Find out the corresponding value of the middle ranking

The following is an example of the use of some complex statements in MySQL, taking the monthly income of employees in a company as an example.

Method one

Create a test table

First, create an income table with the following statement:

CREATE TABLE IF NOT EXISTS `employee` (`id`INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR (10) NOT NULL DEFAULT'', `income` INT NOT NULL DEFAULT'0') ENGINE = InnoDB DEFAULT CHARSET = utf8;INSERT INTO `employee` (`name`, `income`) VALUES ('pockmarked', 20000); INSERT INTO `employee` (`name`, `income`) VALUES ('Li Si', 12000); INSERT INTO `employee` (`name`, `income`) VALUES ('Zhang San', 10000) INSERT INTO `employee` (`name`, `income`) VALUES ('Wang er', 16000); INSERT INTO `employee` (`name`, `income`) VALUES ('Tuhao', 40000)

Complete Task 1

Sort the data and give each row its ranking among all the data:

SELECT t1.name, t1.income, COUNT (*) AS rankFROM employee AS T1, employee AS t2WHERE t1.income < t2.income OR (t1.income = t2.income AND t1.name)