In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
Preface
Today, after synchronizing the order data, my colleague chose to use the 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 because of the difference between the total amount of the order and the total amount of the data source, 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.The following is an example to review the problems encountered.
Problem review
This review will use a very simple order table as an example.
Data preparation
The statement for creating the order table is as follows (lazy here, self-increasing ID is used, 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 (10jue 2) NOT NULL COMMENT' order amount', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Insert a SQL with an amount of 100 as follows (just execute it 10 times)
INSERT INTO `order` (`amount) VALUES (100)
So the total amount is 10-100-1000.
Problem SQL
Use limit to paginate the data, and use the sum () function to calculate the total amount of the current paging
SELECT SUM (`quantit`) FROM `order`order BY `id`LIMIT 5
The running result was also mentioned earlier, and the expected result should be 5-100-100-500, but the actual running result is 1000.00 (with decimal point because of the data type)
Problem troubleshooting
In fact, if you have some knowledge of the order in which SELECT statements are executed, you can quickly determine why the result returned is the total amount of all orders. Below, I will analyze the problem in terms of the preface of the execution book of the question SQL:
The FROM:FROM clause is executed first, confirming that the SELECT:SELECT clause of the table order is the second clause executed, and the SUM () function is executed at this time. The ORDER BY:ORDER BY clause is the third execution clause, and there is only one result, that is, the total order amount LIMIT:LIMIT clause is executed last, and there is only one result in the result set (total order amount).
Supplementary content
Here is a supplement to the execution order of SELECT statements.
FROM ON JOIN WHERE GROUP BY HAVING SELECTDISTINCT ORDER BY LIMIT
Solution.
When you need to count paging data (in addition to the SUM () function, there is also a problem with the common COUNT (), AVG (), MAX (), MIN () functions), you can choose to use subqueries to deal with it (PS: memory calculation is not considered here, but is aimed at using a database to solve this problem). The above solution 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.
Summary
The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.