在 postgresql 中将逗号分隔的 id 转换为逗号分隔的字符串

Question

我有逗号分隔的列，表示紧急类型的ID，例如：

ID | Name
1  | 1,2,3 
2  | 1,2
3  | 1

我想进行查询以获取此值字段的名称。

1 - Ambulance
2 - Fire
3 - Police

预期输出

1 - Ambulance, Fire, Police
2 - Ambulance, Fire
3 - Ambulance

我只需要在 postgresql 中编写 select 语句来显示字符串值而不是逗号分隔的整数值。

Answer 1

逗号分隔值是一种糟糕的数据库设计实践，尽管 postgre 功能非常丰富，您可以轻松处理此任务。

-- just simulate tables
with t1(ID, Name) as(
select 1  ,'1,2,3' union all
select 2  ,'1,2' union all
select 3  ,'1' 
),
t2(id, name) as(
select 1, 'Ambulance' union all
select 2, 'Fire' union all
select 3, 'Police' 
)
-- here is actual query
select s1.id, string_agg(t2.name, ',') from 
( select id, unnest(string_to_array(Name, ','))::INT as name_id from t1 ) s1
join t2
on s1.name_id = t2.id
group by s1.id

演示

不过，如果可以的话，改变你的方法。正确的数据库设计意味着简单的查询和更好的性能。

Answer 2

获取每个 id 的值是一个简单的查询：从 ; 中选择 *。获得值后，您必须使用分隔符"，"解析字符串。然后，您必须将解析的刺痛值分配给适当的职位，并重新制作列表。你是用特定的语言写的吗？

或者你可以只将排序值分配给类似的东西，比如 1,2,3 等于"某个字符串"，1,2 等于"某个其他字符串"，等等。

Answer 3

假设您有一个包含救护车、警察和消防 ID 和值的表，那么您可以使用如下所示的内容。

CREATE TABLE public.test1
(
id integer NOT NULL,
commastring character varying,
CONSTRAINT pk_test1 PRIMARY KEY (id)
);
INSERT INTO public.test1
VALUES (1, '1,2,3'), (2, '1,2'), (3, '1');
CREATE TABLE public.test2
(
id integer NOT NULL,
description character varying,
CONSTRAINT pk_test2 PRIMARY KEY (id)
);
INSERT INTO public.test2
VALUES (1, 'Ambulance'), (2, 'Fire'), (3, 'Police');
with descs as
(with splits as 
(SELECT id, split_part(commastring, ',', 1) as col2, 
split_part(commastring, ',', 2) as col3, split_part(commastring, ',', 3) as col4 from test1)
select splits.id, t21.description as d1, t22.description as d2, t23.description as d3
from splits 
inner join test2 t21 on t21.id::character varying = splits.col2
left join test2 t22 on t22.id::character varying = splits.col3
left join test2 t23 on t23.id::character varying = splits.col4)
SELECT descs.id, CASE WHEN d2 IS NOT NULL AND d3 IS NOT NULL 
THEN CONCAT_WS(',', d1,d2,d3) ELSE CASE WHEN d2 IS NOT NULL 
THEN CONCAT_WS(',', d1,d2) ELSE d1 END END FROM descs
ORDER BY id;

作为解释，我给出了创建表并插入命令，以便您(和其他人(可以遵循逻辑。 如果您在问题中这样做，这将非常有帮助，因为它可以节省每个人的时间并避免误解。

然后，我内心的 CTE 使用split_part拆分字符串。 这里的语法非常简单，字段、分隔符和字段中要拆分的所需列(因此在这种情况下，我们需要一个、两个和三个(。 然后，我将拆分列连接到 test2。 请注意两件事：第一个连接是内部连接，因为在拆分中始终至少会有一列(我假设!!(，而另外两个是左连接;其次，字符变化字段的拆分反过来会产生字符变化拆分，因此我必须将 int id 转换为字符变化才能使连接正常工作。 以这种方式进行转换(即 id 到字符的变化而不是字符到 id 的变化(意味着我不必为空值而烦恼。 最后，根据存在的空值数量，我将结果与给定的分隔符连接起来。 我再次假设 d1 将始终有一个值。

呵呵