PostgreSQL整数数组值使用desc字符串连接到其他表中的整数 [英] PostgreSQL integer array value join to integer in other table with desc string
问题描述
我有一个表 test
列,其中包含整数数组,其值类似于 {1000,4000,6000}
或 {1000}
或称为 ekw
的 {1000,4000}
。
这些值与另一个表中的描述字符串匹配
I have a table test
column with int arrays and values like {1000,4000,6000}
or {1000}
or {1000,4000}
called ekw
.
These values match to a description string in another table
tab: test
id | name | ekw
-----------------
1 | One | {1000}
2 | Two | {1000,4000}
3 | Three | {1000,4000,6000}
tab: ekwdesc
id | value | desc
-----------------
1 | 1000 | Max
2 | 2000 | Tim
3 | 3000 | Rita
5 | 4000 | Sven
6 | 5000 | Tom
7 | 6000 | Bob
是否可以选择这些列并打印字符串?
is it possible to select these columns and print the strings?
类似的东西:
select name, ekw from test, ekwdesc
我希望看到以下结果:
id | name | ekwdesc
-----------------
1 | One | Max
2 | Two | Max, Sven
3 | Three | Max, Sven, Bob
我尝试使用IN和ANY,但无法正常工作。
I tried with IN and ANY but couldn't get it to work.
推荐答案
您正确的想法是使用 any
运算符进行联接。连接完成后,剩下的就是使用 string_agg
将结果转换为所需的格式:
You had the right idea to use the any
operator for the join. Once the join is complete, all that's left is to use string_agg
to transform the result to the format you want:
SELECT name, STRING_AGG(description, ', ')
FROM test
JOIN ekwdesc ON ekwdesc.value = ANY(test.ekw)
GROUP BY name
请参阅附件 SQLFiddle 作为可执行示例。
See the attached SQLFiddle for an executable example.
这篇关于PostgreSQL整数数组值使用desc字符串连接到其他表中的整数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!