在 MySQL 中 CAST 到 DECIMAL

2021-12-31 00:00:00 decimal casting mysql

我正在尝试像这样在 MySQL 中转换为 Decimal:

I am trying to cast to Decimal in MySQL like this:

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

我正在尝试将表中的行数(乘以 1.5)转换为在该点后有两位数的浮点数.

I'm trying to convert the number of rows in a table (times 1.5) to a floating point number with two digits after the point.

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.

再试一次，这个演员也不起作用:

Another try, this cast also doesn't work:

 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将整数转换为小数?

How do I use mysql to cast from integer to decimal?

推荐答案

来自 MySQL 文档:定点类型(精确值)- DECIMAL、NUMERIC:

From MySQL docs: Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC:

在标准 SQL 中，语法 DECIMAL(M) 等价于 DECIMAL(M,0)

In standard SQL, the syntax DECIMAL(M) is equivalent to DECIMAL(M,0)

因此，您要转换为具有 2 个整数位和 0 个小数位的数字.试试这个:

So, you are converting to a number with 2 integer digits and 0 decimal digits. Try this instead:

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

相关文章