英文:
Restrict string_agg order by in postgres
问题
在使用PostgreSQL数据库时，我遇到了一个情况，需要根据存储在表中的逗号分隔的字段ID来显示列名。以下是一个示例：
表1名称：labelprint
id field_id
1 1,2
表2名称：datafields
id field_name
1 年龄
2 姓名
3 性别
为了根据从table1中提取的字段ID（即field_id列中的1,2）来显示字段名，我希望字段名按其相应的ID的顺序显示如下：
期望结果：
id field_id field_name
1 2,1 姓名,年龄
为了实现上述结果，我编写了以下查询：
select l.id,l.field_id ,string_agg(d.field_name,',') as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id
然而，string_agg()函数会按升序对最终字符串进行排序，并显示以下输出：
id field_id field_name
1 2,1 年龄,姓名
正如您所见，字段名列中的顺序未按照field_id值的顺序显示，这是我希望按照field_id值的顺序显示的。
非常感谢您提前的任何建议/帮助！
英文:
While working with postgres db, I came across a situation where I will have to display column names based on their ids stored in a table with comma separated. Here is a sample:
table1 name: labelprint
id field_id
1 1,2
table2 name: datafields
id field_name
1 Age
2 Name
3 Sex
Now in order to display the field name by picking ids from table1 i.e. 1,2 from field_id column, I want the field_name to be displayed in same order as their respective ids as
Expected result:
id field_id field_name
1 2,1 Name,Age
To achieve the above result, I have written the following query:
select l.id,l.field_id ,string_agg(d.field_name,',') as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id
However, the string_agg() functions sort the final string in ascending order and displays the output as shown below:
id field_id field_name
1 2,1 Age, Name
As you can see the order is not maintained in the field_name column which I want to display as per field_id value order.
Any suggestion/help is highly appreciated.
Thanks in advance!
Already mentioned in the description.
答案1
得分: 1
虽然这样可能会对性能、可读性和可维护性造成严重影响，但你可以动态计算你想要的顺序：
select l.id,l.field_id,
string_agg(d.field_name,','
order by array_position(string_to_array(l.field_id::text,','),d.id)
) as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id;
至少你应该将数组实际存储为实际的数组，而不是存储为逗号分隔的字符串。或者使用中间表，根本不要存储数组。
英文:
While this will probably be horrible for performance, as well as readability and maintainability, you can dynamically compute the order you want:
select l.id,l.field_id,
string_agg(d.field_name,','
order by array_position(string_to_array(l.field_id::text,','),d.id)
) as field_names
from labelprint l
join datafields d on d.id = ANY(string_to_array(l.field_id::text,','))
group by l.id
order by l.id;
You should at least store your array as an actual array, not as a comma delimited string. Or maybe use an intermediate table and don't store arrays at all.
答案2
得分: -1
使用对现有查询的轻微修改，您可以按如下方式执行它：
select l.id, l.field_id, string_agg(d.field_name, ',') as field_names
from labelprint l
join datafields d on d.id::varchar = ANY(string_to_array(l.field_id, ','))
group by l.id, l.field_id
order by l.id
英文:
With a small modification to your existing query you could do it as follows :
select l.id, l.field_id, string_agg(d.field_name,',') as field_names
from labelprint l
join datafields d on d.id::varchar = ANY(string_to_array(l.field_id,','))
group by l.id, l.field_id
order by l.id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论