时间戳

首页 > 解决方案 > SQL：显示的小数超出要求

sql - SQL：显示的小数超出要求

问题描述

所以这是我的代码。

SELECT Concat(Round(SUM(Coalesce(Price.Morning, Price.Day, 
Price.Evening)*Tickets.count),2),' €') AS 'Total',
Concat(Round(SUM(Price.Morning * Tickets.count) * 100 / 
(SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count)),2),'%') 
AS 'Riti',
Concat(Round(SUM(Price.Day * Tickets.count) * 100 / 
(SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count)),2),'%') 
AS 'Dienas',
Concat(Round(SUM(Price.Evening * Tickets.count) * 100 / 
(SUM(Coalesce(Price.Morning, Price.Day, 
Price.Evening)*Tickets.count)),2),'%') 
AS 'Evening'
FROM Price,Tickets
WHERE Tickets.Price_ID = Price.Price_ID
;

它显示如下：

图片

一切正常，但我只需要逗号后的 2 个小数，但它仍然显示超过 2 个。

标签： sqlselectnumbersdecimal

解决方案

不要在 SQL 中进行数字格式化。让显示层（应用程序、报表、表单等）进行格式化。它限制了你适应不同文化、使用偏好等的能力。

只需返回原始数据并摆脱格式：

SELECT
    SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count) AS 'Total',
    SUM(Price.Morning * Tickets.count) / 
        SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count) AS 'Riti',
    SUM(Price.Day * Tickets.count) / 
       SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count) AS 'Dienas',
    SUM(Price.Evening * Tickets.count) / 
       SUM(Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count AS 'Evening'
FROM Price,Tickets
WHERE Tickets.Price_ID = Price.Price_ID

Coalesce(Price.Morning, Price.Day, Price.Evening)*Tickets.count您还可以通过将重复计算放在子查询中来简化这一点。

推荐阅读