时间戳

首页 > 解决方案 > 如何在 PostgreSql 数据库中将双精度转换为字符串

sql - 如何在 PostgreSql 数据库中将双精度转换为字符串

问题描述

我想知道我如何将双精度转换为字符串，或者你是否可以做到？我正在使用的代码是：

select 
(select name from locations loc where loc.type = 9 and id in (select id from locations where l.parent = loc.id)) BayName,
    ARRAY_TO_STRING(ARRAY_AGG(DISTINCT l.name), ', ') TroughName,
    ARRAY_TO_STRING(ARRAY_AGG(DISTINCT b.name), ', ') BatchName,
    **ARRAY_TO_STRING(ARRAY_AGG(CAST(sum(case when m.type = 1 and l.name = l.name  then m.amount end)-
    sum(case when m.type = 3 and l.name = l.name  then m.amount else 0 end)-
    sum(case when m.type = 5 and l.name = l.name  then m.amount else 0 end) AS varchar(200))), ', ')** MotherTotalAmount
from batches b 
left outer join phases p on b.id = p.batch
left outer join movements m on b.id = m.batch
left outer join locations l on m.location = l.id
where b.type = 2 and p.type = 13 and b."isActive" = 'true' and m.type in (1, 3 , 5) and (select name from locations loc where loc.type = 9 and id in (select id from locations where l.parent = loc.id)) is not null
group by (select name from locations loc where loc.type = 9 and id in (select id from locations where l.parent = loc.id)), l.name

对于 MothersTotalAmount 字段，输出应该看起来像下面的批处理名称，而不是只给出总数

"bayname"   "troughname"    "batchname" "mothertotalamount"
"13N"   "4" "BLW-002M, CHG-002M, GEL-002M, GJE-003M, GLU-002M, GOG-002M, GSC-002M, GTH-001M, R4-001M, SOD-002M" 89

我需要将总和作为字符串，以便具有与 ARRAY_TO_STRING(ARRAY_AGG(DISTINCT b.name), ', ') BatchName 相同的输出，除非有不同的方法吗？

标签： sqlpostgresqlcasting

解决方案

推荐阅读