强制转换为十进制在MySQL(CAST to DECIMAL in MySQL)

我想转换为十进制在MySQL中是这样的：

CAST((COUNT(*) * 1.5) AS DECIMAL(2))

我想要的行数转换成一个表（1.5倍），以一个浮点数与点后两位数字。

SQL代码：

 SELECT CONCAT(Guardian.title, ' ', 
               Guardian.forename, ' ', 
               Guardian.surname) AS 'Guardian Name', 
               COUNT(*) AS 'Number of Activities', 
               (COUNT(*) * 1.5) AS 'Cost'
 FROM Schedule
 INNER JOIN Child ON Schedule.child_id = Child.id
 INNER JOIN Guardian ON Child.guardian = Guardian.id
 GROUP BY Guardian
 ORDER BY Guardian.surname, Guardian.forename ASC

它会产生一个错误：

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'CAST((COUNT(*) * 1.5) AS DECIMAL(12,2))' at line 1.

另一种尝试，这个转换也不起作用：

 SELECT CONCAT(Guardian.title, ' ', 
               Guardian.forename, ' ', 
               Guardian.surname) AS 'Guardian Name', 
               COUNT(*) AS 'Number of Activities', 
               CAST((COUNT(*) * 1.5) AS DECIMAL(8,2)) AS 'Cost'
 FROM Schedule
 INNER JOIN Child ON Schedule.child_id = Child.id
 INNER JOIN Guardian ON Child.guardian = Guardian.id
 GROUP BY Guardian
 ORDER BY Guardian.surname, Guardian.forename ASC

如何使用MySQL来从一个整数转换成十进制？

Answer 1:

从MySQL文档： 定点类型（精确值） -十进制，数值 ：

在标准SQL，语法DECIMAL(M)相当于DECIMAL(M,0)

所以，你要转换成若干具有2个整数位和0小数位数。 试试这个：

CAST((COUNT(*) * 1.5) AS DECIMAL(12,2)) 

Answer 2:

MySQL的强制转换为十进制：

投射到十进制整数裸：

select cast(9 as decimal(4,2));       //prints 9.00

铸造整数8/5为十进制：

select cast(8/5 as decimal(11,4));    //prints 1.6000

铸造字符串为十进制：

select cast(".885" as decimal(11,3));   //prints 0.885

铸造两个int变量转换为十进制

mysql> select 5 into @myvar1;
Query OK, 1 row affected (0.00 sec)

mysql> select 8 into @myvar2;
Query OK, 1 row affected (0.00 sec)

mysql> select @myvar1/@myvar2;   //prints 0.6250

铸造小数回字符串：

select cast(1.552 as char(10));   //shows "1.552"

Answer 3:

DECIMAL有两个部分： Precision和Scale 。 所以您的查询的部分将是这样的：

CAST((COUNT(*) * 1.5) AS DECIMAL(8,2))

Precision代表的是存储的值显著位数。
Scale表示的可以存储小数点后面的位数。

文章来源: CAST to DECIMAL in MySQL