SQL TRUNCATE函数

TRUNCATE是SQL中的一个数值函数，根据特定的小数点将数字截断。

TRUNCATE函数的语法

SELECT TRUNCATE(X, D) AS Alias_Name;

在TRUNCATE语法中，X是整数，D是小数点。

在SQL中，你也可以使用TRUNCATE函数和表的整数字段一起使用，如下所示：

SELECT TRUNCATE(Integer_Column_Name, Decimal_point ) AS Alias_Name FROM Table_Name;

在这个语法中，我们必须定义表的名称和列，以便在其中执行TRUNCATE函数。

TRUNCATE函数的示例

示例1： 这个示例通过将10.14544截断到3个小数点来获得截断值：

SELECT TRUNCATE(10.14544, 3) AS Truncate_of_10.14544by3;

输出：

Truncate_of_10.14544by4   
---  
101.145   

示例2： 这个例子将908.5478的值截断为两位小数，并将截断后的值返回给result：

SELECT TRUNCATE(908.5478, 2) AS Truncate_of_908.5478by2;

输出：

Truncate_of_908.5478by2   
---  
908.54   

示例3： 在这里，您将得到8.85478的截断值，保留3位小数点：

SELECT TRUNCATE(8.85478, 3) AS Truncate_of_8.85478by3;

输出:

Truncate_of_8.85478by3   
---  
8.854   

示例 4： 该示例将252.54894的值截断为2位小数：

SELECT TRUNCATE(252.54894, 2) AS Truncate_of_252.54894by2;

输出：

Truncate_of_252.54894by200   
---  
252.54   

示例 5： 在这个例子中，我们将对表的整数列执行TRUNCATE函数。

所以，首先我们将创建一个新表，通过这个表我们将执行TRUNCATE函数。

下面的CREATE查询显示了如何在结构化查询语言中创建新表：

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
.......,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

现在，我们使用上述的CREATE语法创建新的表 Student_Sem_Marks ，用于存储大学生每个学期的成绩。

CREATE TABLE Student_Sem_Marks
(
Student_ID INT,
Student_First_Name Varchar(80),
Student_Last_Name Varchar(80),
First_Sem INT,
Second_Sem INT,
Third_Sem INT,
Fourth_Sem INT, 
Total INT, 
Percentage INT,
Grade Varchar(10)
);

下面的多个INSERT查询插入了学生姓名和他们的学期成绩记录：

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11254, Akash, Sharma, 58.857, 65.547, 98.245, 60.254, 282.9038, 70, B1);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11250, Ram, Sharan, 28.247, 58.987, 78.548, 77.254, 243.036, 60, C2);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11255, Manoj, Gupta, 55.847, 25.548, 68.244, 88.785, 238.424, 78, B1);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11257, Parul, Chaudhary, 85.547, 98.278, 62.525, 35.858, 281.935, 65, B2);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11251, Monu, Sharma, 72.254, 63.354, 36.258, 96.639, 268.505, 67, B2);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11258, Srishti, Chaudhary, 85.589, 78.478, 98.785, 89.698, 352.55, 88, A2);

INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11260, Bhavesh, Bardiya, 78.256, 87.658, 82.963, 91.365, 340.242, 85, A2);

下面的SELECT语句显示了上述 Student_Sem_Marks 表中插入的记录：

SELECT * FROM Student_Sem_Marks;  

查询1： 以下SELECT查询使用了上述Student_Sem_Marks表的Total列的TRUNCATE函数：

SELECT Total, TRUNCATE(Total, 2) AS Truncate_of_Total_by2 FROM Student_Sem_Marks;

这个查询将每个学生的总分截断为2位小数。

输出：

查询2: 下面的SELECT查询使用了TRUNCATE函数与上述Student_Sem_Marks表的First_Sem和Second_Sem列：

SELECT First_Sem, TRUNCATE(First_Sem, 1) AS Truncate_ofFirst_Sem, Second_Sem, TRUNCATE(Second_Sem, 1) AS Truncate_ofSecond_Sem FROM Student_Sem_Marks;

这个查询将第一个学期和第二个学期列的值截断为小数点后一位。

输出结果: