mysql踩坑之limit與sum函數(shù)混合使用問題詳解

前言

今天同事在同步完訂單數(shù)據(jù)后，由于訂單總金額和數(shù)據(jù)源的總金額存在差異，選擇使用LIMIT和SUM()函數(shù)計算當(dāng)前分頁的總金額來和對方比較特定訂單的總金額，卻發(fā)現(xiàn)計算出來的金額并不是分頁的訂單總金額，而是所有訂單的總金額。

數(shù)據(jù)庫版本為mysql 5.7，下面會用一個示例復(fù)盤遇到的問題。

問題復(fù)盤

本次復(fù)盤會用一個很簡單的訂單表作為示例。

數(shù)據(jù)準(zhǔn)備

訂單表建表語句如下（這里偷懶了，使用了自增ID，實際開發(fā)中不建議使用自增ID作為訂單ID）

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

插入金額為100的SQL如下（執(zhí)行10次即可）

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

所以總金額為10*100=1000。

問題SQL

使用limit對數(shù)據(jù)進行分頁查詢，同時使用sum()函數(shù)計算出當(dāng)前分頁的總金額

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

前面也提到了運行的結(jié)果，期待的結(jié)果應(yīng)該為5*100=500，然而實際運行的結(jié)果卻為1000.00（帶有小數(shù)點是因為數(shù)據(jù)類型）

問題排查

其實如果對SELECT語句執(zhí)行順序有一定了解的朋友可以很快確定為什么返回的結(jié)果為所有的訂單總金額？下面我會就問題SQL的執(zhí)行書序來分析問題：

1. FROM：FROM子句是最先執(zhí)行的，確定了查詢的是order這張表
2. SELECT：SELECT子句是第二個執(zhí)行的子句，同時SUM()函數(shù)也在此時執(zhí)行了。
3. ORDER BY：ORDER BY子句是第三個執(zhí)行的子句，其處理的結(jié)果只有一個，就是訂單總金額
4. LIMIT：LIMIT子句是最后執(zhí)行的，此時結(jié)果集中只有一個結(jié)果（訂單總金額）

補充內(nèi)容

這里補充一下SELECT語句執(zhí)行順序

1. FROM <left_table>
2. ON <join_condition>
3. <join_type> JOIN <right_table>
4. WHERE <where_condition>
5. GROUP BY <group_by_list>
6. HAVING <having_condition>
7. SELECT
8. DISTINCT <select_list>
9. ORDER BY <order_by_condition>
10. LIMIT <limit_number>

解決辦法

遇到需要統(tǒng)計分頁數(shù)據(jù)時（除了SUM()函數(shù)外，常見的COUNT()、AVG()、MAX()、MIN()函數(shù)也存在這個問題），可以選擇使用子查詢來處理（PS：這里不考慮內(nèi)存計算，針對的是使用數(shù)據(jù)庫解決這個問題）。上面的問題解決方案如下：

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

運行的返回值為500.00。

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對億速云的支持。