Detailed explanation of mixed use of limit and sum functions in mysql stepping pit

Preface

Today, after synchronizing the order data, due to the difference between the total amount of the order and the total amount of the data source, colleagues chose to use LIMIT and SUM () functions to calculate the total amount of the current page to compare the total amount of a specific order with the other party, but found that the calculated amount is not the total amount of the paged order, but the total amount of all orders.

The database version is mysql 5.7, and the following will use an example to repeat the problems encountered.

Problem resumption

This resumption will use a very simple order table as an example.

Data preparation

The statement of order table building is as follows (lazy here, using self-increasing ID, and it is not recommended to use self-increasing ID as order ID in actual development)

CREATE TABLE `order` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ' Order ID',
 `amount` decimal(10,2) NOT NULL COMMENT ' Order amount ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert an SQL with an amount of 100 as follows (execute it 10 times)

INSERT INTO `order`(`amount`) VALUES (100);

So the total amount is 10*100=1000.

Issue SQL

Use limit to page the data, and use sum () function to calculate the total amount of the current page

SELECT 
  SUM(`amount`)
FROM
  `order`
ORDER BY `id`
LIMIT 5;

As mentioned earlier, the expected result should be 5*100=500, whereas the actual result is 1000.00 (with decimal point because of data type)

Problem troubleshooting

In fact, if the SELECT statement execution order has a certain understanding of friends can quickly determine why the return result is the total amount of all orders? Next, I will analyze the problem in terms of the execution sequence of the problem SQL:

FROM: The FROM clause is executed first, which determines that the table order is queried SELECT: The SELECT clause is the second to execute, and the SUM () function is also executed at this time. ORDER BY: The ORDER BY clause is the third executed clause, and the processing result is only one, that is, the total order amount LIMIT: The LIMIT clause is last executed, and there is only one result in the result set (total order amount)

Supplementary content

The execution order of SELECT statement under 1 is added here

FROM < left_table > ON < join_condition > < join_type > JOIN < right_table > WHERE < where_condition > GROUP BY < group_by_list > HAVING < having_condition > SELECT DISTINCT < select_list > ORDER BY < order_by_condition > LIMIT < limit_number >

Solution

When you need to count paging data (in addition to the SUM () function, the common COUNT (), AVG (), MAX (), MIN () functions also have this problem), you can choose to use subqueries to handle it (PS: memory calculation is not considered here, but the database is used to solve this problem). The solution to the above problem is as follows:

SELECT 
  SUM(o.amount)
FROM
  (SELECT 
    `amount`
  FROM
    `order`
  ORDER BY `id`
  LIMIT 5) AS o;

The return value of the run is 500.00.

Summarize