SELECT FORMAT(SUM(quantityOrdered * priceEach),2) total FROM orderdetails WHERE orderNumber = 10100; 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 - mysql> SELECT FORMAT(SUM(quantityOrdered * priceEach),2) total FROM orderdetails WHERE orderNumber = 10100; +-----------+ | total | +-----------+ | 10,223.83 | +-----------+ 1 row in set 請(qǐng)注意，F(xiàn)ORMAT()函數(shù)用于格式化SUM()函數(shù)的返回值。
MySQL SUM與GROUP BY子句 當(dāng)與GROUP BY子句組合時(shí)，SUM()函數(shù)計(jì)算GROUP BY子句中指定的每個(gè)分組的總和。
SELECT orderNumber, FORMAT(SUM(quantityOrdered * priceEach),2) total FROM orderdetails GROUP BY orderNumber ORDER BY SUM(quantityOrdered * priceEach) DESC; 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -
SELECT orderNumber, FORMAT(SUM(quantityOrdered * priceEach),2) FROM orderdetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000 ORDER BY SUM(quantityOrdered * priceEach); 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -
mysql> SELECT orderNumber, FORMAT(SUM(quantityOrdered * priceEach),2) FROM orderdetails GROUP BY orderNumber HAVING SUM(quantityOrdered * priceEach) > 60000 ORDER BY SUM(quantityOrdered * priceEach); +-------------+--------------------------------------------+ | orderNumber | FORMAT(SUM(quantityOrdered * priceEach),2) | +-------------+--------------------------------------------+ | 10310 | 61,234.67 | | 10287 | 61,402.00 | | 10165 | 67,392.85 | +-------------+--------------------------------------------+ 3 rows in set MySQL SUM與LIMIT 假設(shè)您想要計(jì)算products表中前十名最昂貴的產(chǎn)品的總和，可以提出以下查詢：
SELECT SUM(buyprice) FROM products ORDER BY buyprice DESC LIMIT 10; 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -
mysql> SELECT SUM(buyprice) FROM products ORDER BY buyprice DESC LIMIT 10; +---------------+ | SUM(buyprice) | +---------------+ | 5983.47 | +---------------+ 1 row in set 它不起作用，因?yàn)榫哂蠸UM函數(shù)的SELECT語(yǔ)句只返回一行，LIMIT子句約束要返回的行數(shù)無(wú)效。
要解決此問(wèn)題，請(qǐng)使用以下子查詢：
SELECT FORMAT(SUM(buyprice),2) FROM (SELECT buyprice FROM products ORDER BY buyprice DESC LIMIT 10) price; 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -
+-------------------------+ | FORMAT(SUM(buyprice),2) | +-------------------------+ | 958.71 | +-------------------------+ 1 row in set 上面語(yǔ)句是怎么運(yùn)行的？
子查詢選擇十大價(jià)格最高的產(chǎn)品。 外部查詢計(jì)算從子查詢返回的前10個(gè)價(jià)格最高的產(chǎn)品的總和。 MySQL SUM與NULL 如果沒有匹配的行，則SUM函數(shù)返回NULL值。 有時(shí)，您希望SUM函數(shù)返回0而不是NULL。 在這種情況下，可以使用COALESCE函數(shù)。COALESCE函數(shù)接受兩個(gè)參數(shù)，如果第一個(gè)參數(shù)為NULL，則返回第二個(gè)參數(shù)，否則返回第一個(gè)參數(shù); 參考以下查詢語(yǔ)句：
SELECT COALESCE(SUM(quantityOrdered * priceEach),0) FROM orderdetails WHERE productCode = 'S1_212121'; 執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -
mysql> SELECT COALESCE(SUM(quantityOrdered * priceEach),0) FROM orderdetails WHERE productCode = 'S1_212121'; +----------------------------------------------+ | COALESCE(SUM(quantityOrdered * priceEach),0) | +----------------------------------------------+ | 0.00 | +----------------------------------------------+ 1 row in set MySQL SUM與連接語(yǔ)句 您可以使用SELECT JOIN語(yǔ)句中的SUM函數(shù)根據(jù)另一個(gè)表中的值指定的條件來(lái)計(jì)算表中的值的總和。
例如，要計(jì)算取消訂單金額的總和，請(qǐng)使用以下語(yǔ)句：
SELECT FORMAT(SUM(quantityOrdered * priceEach),2) loss FROM orderdetails INNER JOIN orders USING(orderNumber) WHERE status = 'Cancelled' 關(guān)于“mysql如何實(shí)現(xiàn)字段求和”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。
