在 MySQL 中 TRUNCATE()函数

原文:https://www.geeksforgeeks.org/truncate-function-in-mysql/

在本文中，您将看到 TRUNCATE()函数是如何工作的。MySQL 中的 TRUNCATE 函数用于将一个数字截断到指定的小数位数。

语法:

TRUNCATE( X, D)

参数: TRUNCATE()函数接受两个参数，如上所述，如下所述。

  • X–要截断的数字。
  • D–给定数字要被截断到的小数位数。如果为 0，它将移除所有十进制值，只返回整数。如果它是负的，那么数字被截断到小数点的左边。

返回: 返回截断到指定位置后的数字。

示例-1 : 当 D 为 0 时截断数字。

截断负数–

SELECT TRUNCATE(-10.11, 0) AS Truncated_Number ;

输出:

+------------------+
| Truncated_Number |
+------------------+
|              -10 |
+------------------+

截断正数–

SELECT TRUNCATE(100.61, 0) AS Truncated_Number ;

输出:

+------------------+
| Truncated_Number |
+------------------+
|              100 |
+------------------+

示例-2 : 当 D 为负(-ve)时截断数字。 T4【截断负数–

SELECT TRUNCATE(-19087.1560, -3) AS Truncated_Number;

输出:

+------------------+
| Truncated_Number |
+------------------+
|           -19000 |
+------------------+
1 row in set (0.00 sec)

截断正数–

SELECT TRUNCATE(10876.5489, -1) AS Truncated_Number;

输出:

+------------------+
| Truncated_Number |
+------------------+
|            10870 |
+------------------+

示例-3 : 当 D 为正(+ve)时截断数字。 将负数截断到小数点后 2 位–

SELECT TRUNCATE(-7767.1160, 2) AS Truncated_Number;

输出:

+------------------+
| Truncated_Number |
+------------------+
|         -7767.11 |
+------------------+
1 row in set (0.00 sec)

将正数截断到小数点后 3 位–

mysql> SELECT TRUNCATE(17646.6019, 3) AS Truncated_Number;

输出:

+------------------+
| Truncated_Number |
+------------------+
|        17646.601 |
+------------------+

示例-4 : TRUNCATE 函数也可用于查找列数据的截断值。在本例中，我们将查找价格列的截断值。为了演示，创建一个名为产品的表格

CREATE TABLE Product
(
    Product_id INT AUTO_INCREMENT,  
    Product_name VARCHAR(100) NOT NULL,
    Buying_price DECIMAL(13, 6) NOT NULL,
    Selling_price DECIMAL(13, 6) NOT NULL,
        Selling_Date Date NOT NULL,
    PRIMARY KEY(Product_id)

);

现在向产品表中插入一些数据:

INSERT INTO  
Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
    ('P6', 1060.865460, 1700.675400, '2020-08-26' ),
    ('P2', 2000.154300, 3050.986700, '2020-08-27' ),
    ('P1', 4000.874300, 5070.786500, '2020-08-28' ),
    ('P2', 2090.654300, 3050.896500, '2020-09-01' ),
    ('P3', 5900.543280, 7010.654700, '2020-09-04' ),
    ('P4', 4000.353200, 4500.125400, '2020-09-05' ),
    ('P5', 5010.768900, 6000.873200, '2020-09-08' ),
    ('P6', 1060.865460, 1400.675430, '2020-09-11' );

所以产品表为:

mysql> SELECT * FROM Product;

输出:

+------------+--------------+--------------+---------------+--------------+
| Product_id | Product_name | Buying_price | Selling_price | Selling_Date |
+------------+--------------+--------------+---------------+--------------+
|          1 | P6           |  1060.865460 |   1700.675400 | 2020-08-26   |
|          2 | P2           |  2000.154300 |   3050.986700 | 2020-08-27   |
|          3 | P1           |  4000.874300 |   5070.786500 | 2020-08-28   |
|          4 | P2           |  2090.654300 |   3050.896500 | 2020-09-01   |
|          5 | P3           |  5900.543280 |   7010.654700 | 2020-09-04   |
|          6 | P4           |  4000.353200 |   4500.125400 | 2020-09-05   |
|          7 | P5           |  5010.768900 |   6000.873200 | 2020-09-08   |
|          8 | P6           |  1060.865460 |   1400.675430 | 2020-09-11   |
|          9 | P6           |  1060.865460 |   1700.675400 | 2020-08-26   |
|         10 | P2           |  2000.154300 |   3050.986700 | 2020-08-27   |
|         11 | P1           |  4000.874300 |   5070.786500 | 2020-08-28   |
|         12 | P2           |  2090.654300 |   3050.896500 | 2020-09-01   |
|         13 | P3           |  5900.543280 |   7010.654700 | 2020-09-04   |
|         14 | P4           |  4000.353200 |   4500.125400 | 2020-09-05   |
|         15 | P5           |  5010.768900 |   6000.873200 | 2020-09-08   |
|         16 | P6           |  1060.865460 |   1400.675430 | 2020-09-11   |
+------------+--------------+--------------+---------------+--------------+

现在，我们将把买入价格和卖出价格列截断到小数点后两位。

   SELECT  
   Product_name,
   Buying_price,
   TRUNCATE(Buying_price, 2) Trucated_Bprice,  
   Selling_price,
   TRUNCATE(Selling_price, 2) Trucated_Sprice
   FROM Product ;

输出:

+--------------+--------------+-----------------+---------------+-----------------+
| Product_name | Buying_price | Trucated_Bprice | Selling_price | Trucated_Sprice |
+--------------+--------------+-----------------+---------------+-----------------+
| P6           |  1060.865460 |         1060.86 |   1700.675400 |         1700.67 |
| P2           |  2000.154300 |         2000.15 |   3050.986700 |         3050.98 |
| P1           |  4000.874300 |         4000.87 |   5070.786500 |         5070.78 |
| P2           |  2090.654300 |         2090.65 |   3050.896500 |         3050.89 |
| P3           |  5900.543280 |         5900.54 |   7010.654700 |         7010.65 |
| P4           |  4000.353200 |         4000.35 |   4500.125400 |         4500.12 |
| P5           |  5010.768900 |         5010.76 |   6000.873200 |         6000.87 |
| P6           |  1060.865460 |         1060.86 |   1400.675430 |         1400.67 |
| P6           |  1060.865460 |         1060.86 |   1700.675400 |         1700.67 |
| P2           |  2000.154300 |         2000.15 |   3050.986700 |         3050.98 |
| P1           |  4000.874300 |         4000.87 |   5070.786500 |         5070.78 |
| P2           |  2090.654300 |         2090.65 |   3050.896500 |         3050.89 |
| P3           |  5900.543280 |         5900.54 |   7010.654700 |         7010.65 |
| P4           |  4000.353200 |         4000.35 |   4500.125400 |         4500.12 |
| P5           |  5010.768900 |         5010.76 |   6000.873200 |         6000.87 |
| P6           |  1060.865460 |         1060.86 |   1400.675430 |         1400.67 |
+--------------+--------------+-----------------+---------------+-----------------+

版权属于：月萌API www.moonapi.com，转载请注明出处

本文链接：https://www.moonapi.com/news/27643.html