在表中插入带千位分隔符的值

6jjcrrmo  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(398)

我使用以下sql创建一个包含值的表:

CREATE TABLE Logistics (
    country TEXT,
    Costs_Inbound CHAR(255),
    Costs_Storage CHAR(255),
    Costs_Outbound DECIMAL(65,3)

);

INSERT INTO Logistics 
(country, Costs_Inbound, Costs_Storage, Costs_Outbound)
VALUES 
("DE", "5000", "300", "600500.815"),
("NL", "3000", "650", "250452.454"), 
("FR", "4000", "120", "750060.932");

使用以下sql查询表中的值:

SELECT country, sum(Costs_Inbound), sum(Costs_Storage), sum(Costs_Outbound),
       (sum(Costs_Inbound) + sum(Costs_Storage) + sum(Costs_Outbound)) as Total
FROM Logistics
GROUP BY country WITH ROLLUP;

您还可以在 sqlfiddle 在这里。
到目前为止,这一切都很好。
现在,我想把值放入表中,用千位分隔符,而不是 6000500,815 , 250452.454 以及 750060.932 它们应该显示为 600,500.815 , 250,452.454 以及 750,060.932 .
你知道我怎样才能做到吗?

yebdmbv4

yebdmbv41#

使用 format 功能

SELECT country, FORMAT (sum(Costs_Inbound),2) Costs_Inbound, 
FORMAT(sum(Costs_Storage),2) Costs_Storage,
FORMAT(sum(Costs_Outbound), 2) Costs_Outbound,
      FORMAT( (sum(Costs_Inbound) + sum(Costs_Storage) + sum(Costs_Outbound)),2) as Total
FROM Logistics
GROUP BY country WITH ROLLUP

http://sqlfiddle.com/#!9/92d35b2/7号公路

相关问题