sql - 具有数组元素的数组类型的 PostgreSQL JOIN

Question

我有两个表标签和用户

表名:标签

| id | name  |  
| 1  | one   |   
| 2  | two   |   
| 3  | three |   
| 4  | four  |   
| 5  | five  |   

表名:users

| id | fname | tags      |  
| 1  | Ram   | {1,5}     |
| 2  | Sham  | {1,2,3,4} |
| 3  | Bham  | {1,3}     |
| 4  | Kam   | {5,2}     |
| 5  | Lam   | {4,2}     |

预期输出:

| id | fname | tags                  |
| 1  | Ram   | one, five             |
| 2  | Sham  | one, two, three, four |
| 3  | Bham  | one, three            |
| 4  | Kam   | five, two             |
| 5  | Lam   | four, two             |

试验 1:使用 JOIN

SELECT I.id, I.fname, I.tags, J.name FROM users I 
JOIN tags J ON J.id = ANY(I.cached_tag_ids) 
LIMIT 1

结果:

| id | fname | tags |
| 1  | Ram   | one  |
| 1  | Ram   | five |

预期:

| id | fname | tags       |
| 1  | Ram   | one, five  |

Answer 1

您的标签 应该有一个INTEGER[] 类型。

CREATE TABLE users(
   id SERIAL,
   fname VARCHAR(50),
   tags INTEGER[]
);

然后，

SELECT I.id, I.fname, array_agg(J.name) 
FROM users I 
LEFT JOIN tags J 
ON J.id = ANY(I.tags) 
GROUP BY fname,I.id ORDER BY id

应该可以。参见 sqlfiddle

这question可能有帮助。