sql - 如何选择带有连接列的表？

Question

我有以下数据:

select * from art_skills_table;
+----+------+---------------------------+
| ID | Name | skills                    | 
+----+------+---------------------------|
|  1 | Anna | ["painting","photography"]| 
|  2 | Bob  | ["drawing","sculpting"]   |
|  3 | Cat  | ["pastel"]                |
+----+------+---------------------------+

select * from computer_table;
+------+------+-------------------------+
| ID   | Name | skills                  |  
+------+------+-------------------------+
|    1 | Anna |   ["word","typing"]     |
|    2 | Cat  |   ["code","editing"]    |
|    3 | Bob  |   ["excel","code"]      |
+------+------+-------------------------+

我想写一个 SQL 语句，结果如下表。

+------+------+-----------------------------------------------+
| ID   | Name | skills                                        |  
+------+------+-----------------------------------------------+
|    1 | Anna |   ["painting","photography","word","typing"]  |
|    2 | Bob  |   ["drawing","sculpting","excel","code"]      |
|    3 | Cat  |   ["pastel","code","editing"]                 |
+------+------+-----------------------------------------------+

我尝试过类似SELECT * from art_skills_table LEFT JOIN computer_table ON name。但是它没有提供我需要的东西。我读过有关 array_cat 的内容，但我在实现它时遇到了一些麻烦。

Answer 1

如果两个表中的 skills 列都是数组，那么您应该能够解决这个问题:

SELECT a.ID, a.name, array_cat(a.skills, c.skills)
FROM art_skills_table a LEFT JOIN computer_table c 
ON c.id = a.id

也就是说，虽然您在示例中使用了 LEFT 联接，但我认为 INNER 或 FULL (OUTER) 联接可能更适合您。